TSQL and reading SQL server object properties : Like ADO can ?

• Digs

  SSCertifiable

  Points: 7333

  More actions

  February 12, 2005 at 6:50 pm

  #163735

  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 !!

• Kathi Kellenberger

  SSChampion

  Points: 11811

  More actions

  February 13, 2005 at 2:35 pm

  #541964

  Look in to "Information Schema Views"

   

  Aunt Kathi Data Platform MVP
  Author of Expert T-SQL Window Functions
  Simple-Talk Editor

• Fred Williams-134192

  SSCrazy

  Points: 2532

  More actions

  February 14, 2005 at 9:30 am

  #542104

  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