February 12, 2005 at 6:50 pm
I know that using ADO , you can get all the info of a SQL server object, you can it is name, type, size, schema, etc
But can you do this in TSQL, search a system table or master db to find if a table field is this or a table schema is that ?
Any examples or articles, just point me in the correct direction, thanks.
This forum is excellent...learning heaps !!
February 13, 2005 at 2:35 pm
Look in to "Information Schema Views"
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 14, 2005 at 9:30 am
Check out code from a "describe" procedure on http://www.sqlsavior.com/describe.html:
select
convert(varchar(60), c.name) as 'column name',
case t.name
when 'varchar' then convert(varchar(17), 'varchar(' + convert(varchar, c.length) + ')' )
when 'nvarchar' then convert(varchar(17), 'nvarchar(' + convert(varchar, c.length/2) + ')' )
when 'numeric' then convert(varchar(17), 'numeric(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale) + ')' )
when 'decimal' then convert(varchar(17), 'decimal(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale) + ')' )
when 'char' then convert(varchar(17), 'char(' + convert(varchar, c.length) + ')' )
when 'nchar' then convert(varchar(17), 'nchar(' + convert(varchar, c.length/2) + ')' )
when 'binary' then convert(varchar(17), 'binary(' + convert(varchar, c.length) + ')' )
when 'varbinary' then convert(varchar(17), 'varbinary(' + convert(varchar, c.length) + ')' )
else convert(varchar(17), t.name)
end as 'type',
case isnullable when 1 then ' NULL,' else 'NOT NULL,' end as 'nulls'
from syscolumns c, systypes t
where c.id = @table_id
and c.xtype = t.xtype
and t.xtype = t.xusertype
order by colid
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply