How To List Database/Table/Primary Key using T-SQL in SQL Server 2012

EXECUTE master.dbo.sp_msforeachdb
      'USE [?];
      SELECT O.name AS TableName,
            I.name AS PrimaryKeyName
      FROM sys.objects AS O
      INNER JOIN sys.indexes AS I
            ON O.object_id = I.object_id
            AND I.is_primary_key = 1
      WHERE O.[type] = ''U'''

Brian-
That looks good but  I also need the database name.

EXECUTE master.dbo.sp_msforeachdb
      'USE [?];
      SELECT ''?'' AS DatabaseName, O.name AS TableName,
            I.name AS PrimaryKeyName
      FROM sys.objects AS O
      INNER JOIN sys.indexes AS I
            ON O.object_id = I.object_id
            AND I.is_primary_key = 1
      WHERE O.[type] = ''U'''

If you want them all in the same resultset then you can have them insert into a temp table instead of just doing a select

Brian-
Yes, I would like to have them in the same result set.

And I see I was not clear enough. I need to have the name of the  column which is the primary key instead of the name of the primary key itself.

try this...I chose to display the column name as a comma-delimited list in the case that there are multiple columns in the PK

EXECUTE master.dbo.sp_msforeachdb
	'USE [?];
	SELECT ''?'' AS DatabaseName, T.name AS TableName,
		I.name AS PrimaryKeyName,
		STUFF(
		(
			SELECT '','' + C.name
			FROM sys.index_columns AS IC
			INNER JOIN sys.columns AS C
				ON IC.[object_id] = C.[object_id]
				AND IC.column_id = C.column_id
			WHERE IC.[object_id] = I.[object_id]
				AND IC.index_id = I.index_id
			ORDER BY IC.key_ordinal
			FOR XML PATH('''')
		), 1, 1, '''') AS PrimaryColumnList
	FROM sys.tables AS T
	INNER JOIN sys.indexes AS I
		ON T.[object_id] = I.[object_id]
		AND I.is_primary_key = 1
	WHERE T.[type] = ''U''
	ORDER BY T.name'

Select allOpen in new window

In the same resultset...

CREATE TABLE #PrimaryKey
(
	DatabaseName	VARCHAR(128),
	TableName		VARCHAR(128),
	PKColumnList	VARCHAR(MAX)
);

EXECUTE master.dbo.sp_msforeachdb
	'USE [?];
	INSERT #PrimaryKey (DatabaseName, TableName, PKColumnList)
	SELECT ''?'' AS DatabaseName, T.name AS TableName,
		STUFF(
		(
			SELECT '','' + C.name
			FROM sys.index_columns AS IC
			INNER JOIN sys.columns AS C
				ON IC.[object_id] = C.[object_id]
				AND IC.column_id = C.column_id
			WHERE IC.[object_id] = I.[object_id]
				AND IC.index_id = I.index_id
			ORDER BY IC.key_ordinal
			FOR XML PATH('''')
		), 1, 1, '''') AS PrimaryKeyColumnList
	FROM sys.tables AS T
	INNER JOIN sys.indexes AS I
		ON T.[object_id] = I.[object_id]
		AND I.is_primary_key = 1
	WHERE T.[type] = ''U''
	ORDER BY T.name'

SELECT * FROM #PrimaryKey

Select allOpen in new window

That is working well except I need to have just one result set. Is that possible?

Never mind. I missed copying part of the code. That works great.

Excluding system databases is a nice touch.

Great solution; exactly what I wanted. Thank  you Brian.