Search

Usually, in our system, we use , for our primary key some kind of notation/coding for it, for example : xxxID.
So, given 3 liters + ‘ID’ , return the name of the table(s) , from each database existing in the instance.

For this, I have to search in each database, and I use some code from Aaron Bertrand , credit to him for this . link1 , link2.

CREATE
OR
ALTER PROCEDURE [dbo].[sp_PrimaryKeySearch] @pkColName nvarchar(128) 
AS 
BEGIN 
DECLARE @nvc_SQLTEXT nvarchar(2000) =N'
		IF EXISTS(SELECT *
					FROM sys.key_constraints as kc 
					INNER JOIN sys.columns as c
					ON c.object_id = kc.parent_object_id
					WHERE kc.type =''PK'' AND c.name = '''+ @pkColName +''')
		BEGIN
			SELECT DB_NAME() as dbName, STRING_AGG(PKnUTable.name,'','') as lstTabele
			FROM sys.key_constraints as PKnUKEY
				INNER JOIN sys.tables as PKnUTable
					ON PKnUTable.object_id = PKnUKEY.parent_object_id
				INNER JOIN sys.index_columns as PKnUColIdx
					ON PKnUColIdx.object_id = PKnUTable.object_id
					AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
				INNER JOIN sys.columns as PKnUKEYCol
					ON PKnUKEYCol.object_id = PKnUTable.object_id
					AND PKnUKEYCol.column_id = PKnUColIdx.column_id
			WHERE PKnUKEY.type = ''PK''
				AND PKnUKEYCol.name = '''+ @pkColName +'''
		END
		'; /*PRINT @nvc_SQLText;*/ 
                EXEC sp_inEachDB @nvc_SQLTEXT
 END

And add this code to the SSMS , to query shortcuts…and done.

S