Systems Engineering and RDBMS

One more meta-data script for SQL Server 2005

Posted by decipherinfosys on March 26, 2007

Here’s another script to get a list of all the tables, their columns, nullability constraint, data-type, data-type lengths and column default values:

select table_name, column_name, is_nullable, data_type,
case when data_type in (‘int’, ‘smallint’, ‘tinyint’, ‘bigint’ ) then cast(numeric_precision as varchar(10))
when data_type in (‘numeric’, ‘decimal’, ‘float’ ) then ‘Numeric(‘ + cast(numeric_precision as varchar(10)) + ‘,’ + cast(numeric_scale as varchar(10))+ ‘ )’
when data_type in (‘datetime’, ‘smalldatetime’) then cast(datetime_precision as varchar(10))
when data_type = ‘bit’ then ‘1’
when data_type in (‘char’, ‘nchar’, ‘text’, ‘ntext’, ‘varchar’, ‘nvarchar’ ) then case cast(character_maximum_length as varchar(10))
when -1 then ‘varchar(max)’
else cast(character_maximum_length as varchar(10))
end
when data_type = ‘xml’ then ‘**XML**’
when data_type = ‘sql_variant’ then ‘**Variable data-type**’
else data_type
end as data_type_length,
column_default as Default_Value
from information_schema.columns
where table_name in (select table_name from information_schema.tables where table_type = ‘Base Table’)
order by table_name, ordinal_position

Sorry, the comment form is closed at this time.

 
%d bloggers like this: