USE [master]
GO
IF OBJECT_ID('dbo.sp_PrimaryKeyWhere') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_PrimaryKeyWhere AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_PrimaryKeyWhere
@TableName SYSNAME = NULL,
@KeyValues VARCHAR(MAX) = NULL,
@Alias VARCHAR(30) = 'a',
@SQL VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @TableName IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_PrimaryKeyWhere',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
PRINT '/*@KeyValues = (230, 2407470, 8, 1)*/
GO'
RETURN
END
SELECT @Alias = CASE WHEN @Alias <> '' THEN REPLACE(@Alias, '.', '')
ELSE ''
END,
@TableName = Util.dbo.TrimBothEnds(@TableName)
SELECT @SQL = 'SELECT *
FROM [' + s.name + '].[' + t.name + '] ' + @Alias + ' (NOLOCK)
' + ISNULL('WHERE ' + Util.dbo.StringConcat(ISNULL(NULLIF(@Alias, '') + '.', '') + '[' + CASE WHEN ic.key_ordinal IS NOT NULL THEN C.NAME
END + '] = ' + ISNULL(INPUT, 'NULL'), '
AND '), '-- NO PRIMARY KEY FOUND')
FROM sys.schemas s (NOLOCK)
INNER JOIN sys.tables t (NOLOCK) ON s.SCHEMA_ID = t.SCHEMA_ID
INNER JOIN sys.columns c (NOLOCK) ON c.OBJECT_ID = t.OBJECT_ID
INNER JOIN sys.types ty (NOLOCK) ON c.user_type_id = ty.user_type_id
CROSS APPLY (SELECT CASE WHEN ty.name LIKE '%date%'
OR ty.name LIKE '%time%'
OR c.PRECISION = 0 THEN 1
ELSE 0
END AS QuoteInput) tk
LEFT OUTER JOIN sys.indexes AS ix (NOLOCK) ON t.OBJECT_ID = ix.OBJECT_ID
AND ix.index_id = 1
LEFT OUTER JOIN sys.index_columns AS ic (NOLOCK) ON ic.OBJECT_ID = ix.OBJECT_ID
AND ic.index_id = ix.index_id
AND ic.column_id = c.column_id
LEFT OUTER JOIN (SELECT LTRIM(RTRIM(d.Field)) AS Field,
FieldNum
FROM (SELECT LTRIM (RTRIM (@KeyValues)) AS p1) a
CROSS APPLY (SELECT CASE WHEN LEFT (p1, 1) = '(' THEN SUBSTRING (p1, 2, LEN (p1)) ELSE p1 END AS p2) b
CROSS APPLY (SELECT CASE WHEN RIGHT (p2, 1) = ')' THEN LEFT (p2, LEN (p2) - 1) ELSE p2 END AS p3) c
CROSS APPLY UTIL.DBO.ParseDelimited(p3, ',') d) p ON p.FieldNum = ic.key_ordinal
CROSS APPLY (SELECT CASE WHEN QuoteInput = 1 THEN '''' + REPLACE (fIELD, '''', '') + '''' ELSE Field END AS INPUT) k
WHERE t.OBJECT_ID = OBJECT_ID(@TableName)
GROUP BY s.name,
T.NAME
EXEC Util.dbo.PrintLargeText
@SQL
GO
EXEC sys.sp_ms_marksystemobject
sp_PrimaryKeyWhere
GO