PKs using a Cursor
-
This shows you all of your PKs in a database. Couldn't find anything else on this (thanks to a guy on SQLTeam.com tho).
-- Declare the variables to store the values returned by FETCH.
DECLARE @tableVar varchar (50)
DECLARE table_cursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN table_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM table_cursor
INTO @tableVar
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
-- PRINT @table_qualifier + ' ' + @table_owner + ' ' + @table_name + ' ' @column_name
--exec sp_pkeys @tableVar
print @tableVar
SELECT
tc.TABLE_SCHEMA
,tc.TABLE_NAME
,tc.CONSTRAINT_NAME
,kcu.COLUMN_NAME
,kcu.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON TC.CONSTRAINT_CATALOG =KCU.CONSTRAINT_CATALOG
AND TC.CONSTRAINT_SCHEMA =KCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE
tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
and tc.TABLE_NAME = @tableVar
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM table_cursor
INTO @tableVar
END
CLOSE table_cursor
DEALLOCATE table_cursor
GO
-
What's the question??
-
Yo Remi...no question, just a general post of how to do this because I couldn't find it anywhere else. If you feel inclined to bend your mind a bit, make this work with sp_pkeys in the cursor.
-
You mean something like this??
SELECT
c.name
, O.id
, case when I.indid BETWEEN 1 AND 254 AND (I.status & 2048 = 2048 or I.Status = 16402 /*clustered + unique*/ and O.XType = 'V') then 1 else 0 end as IsPK
, USER_NAME(O.uid) as Owner
, C.colid
, ik.indid
, c.length
, isnull(c.prec,0) as ColPrecision
, t.name as VarTypeName
--could be converted to a left join... someday
, (Select Text from dbo.syscomments CM where CM.id = C.cDefault) as DefaultValue
, ik.keyno
FROM dbo.syscolumns C INNER JOIN
dbo.sysobjects O INNER JOIN
dbo.sysindexes I INNER JOIN
dbo.sysindexkeys ik ON I.id = ik.id AND ik.indid = I.indid
ON O.id = I.id
ON C.id = O.id AND C.colid = ik.colid INNER JOIN
dbo.systypes t ON c.xtype = t.xtype
WHERE O.status >= 0
AND LEFT(I.name, 8) '_WA_Sys_'
-
OR:
SELECT
t.TABLE_SCHEMA
,t.TABLE_NAME
,tc.CONSTRAINT_NAME
,kcu.COLUMN_NAME
,kcu.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLES t
JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON tc.TABLE_NAME = t.TABLE_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON TC.CONSTRAINT_CATALOG =KCU.CONSTRAINT_CATALOG
AND TC.CONSTRAINT_SCHEMA =KCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE
tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
t.TABLE_NAME
,tc.CONSTRAINT_NAME
, kcu.ORDINAL_POSITION
* Noel -
there's always a but 
.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply