Friday, May 11, 2007

Primary and Index Keys at SQL Server 2005

While doing the documentation, I came across to find the most efficient to get all tables within database, with respective tables,column type, and etc. Finally, I be able to write scripts to get PRIMARY and INDEXES Keys for my documentation. It saves a lot of time

-- Getting Primary Key
SELECT SysObj.name AS [Table_Name]
,SysIX.Name AS [Index_Name]
,SysCOl.name as [Columns]
FROM sysobjects AS SysObj
INNER JOIN sysindexes AS SysIX ON SysObj.id = SysIX.id
INNER JOIN sys.sysindexkeys AS SysIXKey ON SysIX.indid= SysIXKey.indid AND SysIX.id= SYSIXKey.id
INNER JOIN syscolumns SysCol ON SySCol.colid= SysIXKey.ColId AND SysIXKey.id = SysCol.ID
WHERE SysObj.id > 100
and SysIX.name LIKE 'PK%'
ORDER BY SysObj.name,SysIX.Name,SysIXKey.KeyNo
-- Getting Index Key
SELECT SysObj.name AS [Table_Name]
,SysIX.Name AS [Index_Name]
,SysCOl.name as [Columns]
FROM sysobjects AS SysObj
INNER JOIN sysindexes AS SysIX ON SysObj.id = SysIX.id
INNER JOIN sys.sysindexkeys AS SysIXKey ON SysIX.indid= SysIXKey.indid AND SysIX.id= SYSIXKey.id
INNER JOIN syscolumns SysCol ON SySCol.colid= SysIXKey.ColId AND SysIXKey.id = SysCol.ID
WHERE SysObj.id > 100
and SysIX.name LIKE 'IX%'
ORDER BY SysObj.name,SysIX.Name,SysIXKey.KeyNo



No comments: