Dynamic SQL Query to pull Primary Key Name

Question

I am working on a much larger query that will build out C# functions and classes for me but I am running into issues with one small part, I need to rewrite the query below so that specifically the part "@DataBaseName.INFORMATION_SCHEMA.KEY_COLUMN_USAGE" will work. Obviously the current way it is written will not work but for the life of me I cannot figure out how to change it to get it to work. Just a quick note if I were to just type out the query with the actual DB name and DB Table name it will work but I need to be able to store both of those in variables at the beginning to accomplish the task I am looking for. This is for MS SQL Server 2008 R2

declare @TableName sysname = 'ENTERTABLENAME'
declare @DataBaseName sysname = 'ENTERDATABASENAME'

/* Start of Update Function Created */

declare @PrimaryKey varchar(max) = ''
 SELECT @PrimaryKey = COLUMN_NAME FROM @DataBaseName.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @TableName AND CONSTRAINT_NAME LIKE 'PK%'

Are you aware that Primary keys can be composite? Have multiple columns? If all your PK are single column and `IDENTITY` there is a much simpler way as well. — Martin Smith, Dec 12 '14 at 17:04
You are correct, there can be more than one primary key and it is not always an IDENTITY, So the ultimate result here is it is building out an UPDATE statement So I will be using the primary key(s) to use for the "WHERE" portion of the Update statement. Example "UPDATE TABLENAME SET FIELD1=@FIELD1 WHERE FIELD2 = @FIELD2" or if there are more than one Primary Key "UPDATE TABLENAME SET FIELD1=@FIELD1 WHERE FIELD2 = @FIELD2 AND FIELD3=@FIELD3" — scripter78, Dec 12 '14 at 17:20
Possible duplicate of [How do you list the primary key of a SQL Server table?](https://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table) — GSerg, Oct 04 '19 at 12:49

score 1 · Accepted Answer · answered Dec 12 '14 at 18:02

You need to use Dynamic SQL

DECLARE @TableName SYSNAME = 'Address'
DECLARE @DataBaseName SYSNAME = 'AdventureWorks2008'

DECLARE @PrimaryKey VARCHAR(max) = '',
        @sql        NVARCHAR(max)

SET @sql ='SELECT @PrimaryKey += '',''+COLUMN_NAME FROM '
          + @DataBaseName + '.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = '''
          + @TableName
          + ''' AND CONSTRAINT_NAME LIKE ''PK%'''

--print @sql
EXEC Sp_executesql
  @sql,
  N'@PrimaryKey varchar(1000) output',
  @PrimaryKey output

SELECT RIGHT(@PrimaryKey, Len(@PrimaryKey) - 1)

Now that looks simple. Thank you ! – scripter78 Dec 12 '14 at 19:32 — scripter78, Dec 12 '14 at 19:32

Dynamic SQL Query to pull Primary Key Name

1 Answers1