USE [master]
GO
IF OBJECT_ID('dbo.sp_SingleLookupProc') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_SingleLookupProc AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_SingleLookupProc
@TableName VARCHAR(256) = NULL,
@PrintSQL BIT = 1,
@SQL VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
DECLARE @objectid INT = OBJECT_ID(Util.dbo.TrimBothEnds(@TableName))
IF @objectid IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_SingleLookupProc',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
SELECT @SQL = (SELECT 'IF OBJECT_ID(''' + QUOTENAME(s.NAME) + '.' + QUOTENAME('Get' + o.NAME) + ''') IS NULL EXEC(''CREATE PROCEDURE ' + QUOTENAME(s.NAME)
+ '.' + QUOTENAME('Get' + o.NAME) + ' AS RETURN'')
GO
ALTER PROCEDURE ' + QUOTENAME(s.NAME) + '.' + QUOTENAME('Get' + o.NAME) + '
' + Util.dbo.StringConcat(' @' + c.NAME + ' ' + ct.ColumnType + ' = NULL OUTPUT', ',
') + ',
@RowCount INT = 0 OUTPUT
AS
SET NOCOUNT ON
SELECT
' + Util.dbo.StringConcat(' @' + c.NAME + ' = ' + c.NAME, ',
') + '
FROM ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(o.NAME) + '
'
--s.NAME,o.name,
--c.NAME,
-- ct.columntype,
-- c.is_nullable
FROM sys.columns c (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON o.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
INNER JOIN sys.types y (NOLOCK) ON y.user_type_id = c.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(y.name, c.max_length, c.PRECISION, c.scale, c.collation_name, db.collation_name) ct
WHERE c.OBJECT_ID = @objectid
GROUP BY s.NAME,
o.NAME) + (SELECT 'WHERE ' + Util.dbo.StringConcat(UniqueIndexColumn + ' = @' + UniqueIndexColumn, ' OR ') + '
SET @RowCount = @@ROWCOUNT'
FROM (SELECT DISTINCT
Util.dbo.StringConcat(c.NAME, '') AS UniqueIndexColumn
FROM sys.indexes i (NOLOCK)
INNER JOIN sys.index_columns ic (NOLOCK) ON ic.index_id = i.index_id
AND ic.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.columns c (NOLOCK) ON c.OBJECT_ID = i.OBJECT_ID
AND c.column_id = ic.column_id
WHERE i.OBJECT_ID = @objectid
AND (is_unique = 1
OR is_primary_key = 1
OR is_unique_constraint = 1)
GROUP BY i.index_id
HAVING COUNT(*) = 1) K) + '
GO'
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
GO
EXEC sys.sp_ms_marksystemobject
sp_SingleLookupProc
GO