How to retrieve some information using SQL on any table in a database on SQL-Server 2K.

I see a few questions on this, and it get asked how to do this way too often. So i’ve decided to put this out for everyone who is interested. If you have any questions then i’ll answer them in the comments area.

Enjoy.

declare  @tblName varchar(64)



select    @tblName = 'SI_StockItem'





select    isnull(drvd1.Colid,0)    as IsPrimaryKey,

            SN.Name                    as ColumnName,

            ST.name                    as DBType,

            SN.Length                  as Length,

            1-ST.allownulls            as AllowNull,

            isnull(SC.text,'')          as DefaultValue,

            isnull(ST.Collation,'')   as Collation

from     syscolumns  SN

           inner join  dbo.sysobjects as TN

                    on  SN.[id] = TN.[id]

            inner join dbo.systypes AS ST

                    on   ST.[xusertype] = SN.xtype

            left join   dbo.syscomments As SC

                    on   sc.[Id] = SN.cDefault

            left join   (

                              select    colId,

                                          SK.id

                              from     dbo.sysindexkeys as SK

                                          inner join sysindexes as SI

                                                   on SK.indid = SI.indID

                                          inner join sysobjects as SO

                                                   on SI.[name] = SO.[name]

                                                 and SK.id = SO.parent_obj

                              where     SO.xtype = 'PK'

                        ) drvd1

                        on drvd1.colId =SN.colId

                        and drvd1.id = TN.[id]

where    SN.id

            =

            (

                   select   id

                   from     sysobjects

                   where    name=@tblName

            )

            order by SN.colid