Home > SQL-Server 2005 > SQL Server – Metadaten – Feldtypen feststellen

SQL Server – Metadaten – Feldtypen feststellen

Ein kleines Script als Table-Values-Function dass für eine Tabelle oder ein Feld den Datentype zurückgibt:


create function tvf_GetFieldTypes(@SchemaName sysname,@TabellenName sysname,@Feldname sysname)
returns table as return
select   s.name as SchemaName
        ,o.name as TabellenName
        ,c.name as FeldName
        ,t.name + 
            case t.name
                when 'real'      then '(' + cast(c.precision as varchar(10)) + ')'

                when 'varbinary' then '(' + cast(c.max_length as varchar(10)) + ')'
                when 'binary'    then '(' + cast(c.max_length as varchar(10)) + ')'
                when 'varchar'   then '(' + cast(c.max_length as varchar(10)) + ')'
                when 'char'      then '(' + cast(c.max_length as varchar(10)) + ')'
                when 'nvarchar'  then '(' + cast(c.max_length/2 as varchar(10)) + ')'
                when 'nchar'     then '(' + cast(c.max_length/2 as varchar(10)) + ')'

                when 'numeric'    then '(' + cast(c.precision as varchar(10)) + ',' + 
                                             cast(c.scale as varchar(10)) + ')'
                when 'decimal'    then '(' + cast(c.precision as varchar(10)) + ',' + 
                                             cast(c.scale as varchar(10)) + ')'
                when 'money'      then '(' + cast(c.precision as varchar(10)) + ',' + 
                                             cast(c.scale as varchar(10)) + ')'
                when 'smallmoney' then '(' + cast(c.precision as varchar(10)) + ',' + 
                                             cast(c.scale as varchar(10)) + ')'
                else ''
            end as FeldTyp
        ,case c.is_nullable when 0 then 'NOT NULL' else 'NULL' end as Nullable
        
from sys.objects o
    join sys.schemas s on s.schema_id = o.schema_id
    join sys.columns c on c.object_id = o.object_id
    join sys.types   t on t.system_type_id = c.system_type_id
where o.name like '%' + @TabellenName + '%'
  and s.name like '%' + @SchemaName + '%'
  and c.name like '%' + @Feldname + '%'
  and t.system_type_id = t.user_type_id
go

select * from tvf_GetFieldTypes('Person','Co','')


und hier die Ausgabe:

Ergebnis
SchemaName TabellenName FeldName FeldTyp Nullable
Person Contact ContactID int NOT NULL
Person Contact NameStyle bit NOT NULL
Person Contact Title nvarchar(8) NULL
Person Contact FirstName nvarchar(50) NOT NULL
Person Contact MiddleName nvarchar(50) NULL
Person Contact LastName nvarchar(50) NOT NULL
Person Contact Suffix nvarchar(10) NULL
Person Contact EmailAddress nvarchar(50) NULL
Person Contact EmailPromotion int NOT NULL

 

have fun

KategorienSQL-Server 2005