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