I encountered a requirement in my work. I had to query the table structure, index, and primary key, but it was suddenly difficult because the script I wrote for the first time was only compatible with SQL Server 2005, in SQL Server 2000, an error is reported. If it is depressing, I will modify it again. This time it is common. I dare to report an error to you! -- Query indexes and primary keys Select
Indexid = idx. indid,
Indexname = idx. Name,
Columnname = col. Name,
Sort = case indexkey_property (idx. ID, idx. indid, idxk. keyno, 'isdesending ')
When 1 then 'desc'
When 0 then 'asc'
Else ''end,
Primarykey = case objpk. xtype
When 'pk 'then' √'
Else ''end
From
Sysindexes idx
Inner join sysobjects C
On idx. ID = C. ID
And C. xtype = 'U'
Left join sysobjects objpk
On objpk. [name] = idx. [name]
Left join sysindexkeys idxk
On idx. ID = idxk. ID
And idx. indid = idxk. indid
Left join syscolumns col
On col. colid = idxk. colid
And col. ID = idxk. ID
Where
C. Name = 'tablename' -- change the table name here.
And col. Name is not null -- Query the table structure Select
Id = col. colorder,
[Name] = col. Name,
[Systemtype] = types. Name,
[Length] = cast (case when types. Name in (N 'nchar ', N 'nvarchar') and Col. Length <>-1 then col. Length/2
Else col. Length End
As INT)
From
Syscolumns col
Left join policypes types
On col. xtype = types. xusertype
Inner join sysobjects OBJ
On col. ID = obj. ID
And obj. xtype = 'U'
And obj. Name <> 'dtproperties'
Where
OBJ. Name = 'tablename' -- change the table name here. Order
Col. colorder
