Showing posts with label SQL Server 2005. Show all posts
Showing posts with label SQL Server 2005. Show all posts

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