Generate SQL with system views.

WHERE from composite PRIMARY_KEY

This is the second post in my series on using system views to generate SQL.

I’ve relied on this approach for years to speed up development and keep queries aligned with database rules. Over time I’ve built a set of stored procedures that handle different SQL generation tasks, making it easier to stay productive and consistent.

Below SQL uses a composite PRIMARY_KEY to generate an WHERE clause.

It´s simplified but it shows the main concept, in my SQL generation stored procedures there is more dynamic SQL to be able to use parameters for table names and more.

If you run this in SSMS, check the option for "Result to text (CTRL + T)" to view the generated SQL.

DROP TABLE IF EXISTS [CustomerInvoices];
CREATE TABLE [CustomerInvoices]
(
	[CustomerId] NVARCHAR(100) NOT NULL
	, [InvoiceNo] BIGINT NOT NULL
	, [InvoiceSum] MONEY NOT NULL DEFAULT(0)
	CONSTRAINT [PK_CustomerInvoices] PRIMARY KEY CLUSTERED ([CustomerId], [InvoiceNo])
);

;WITH CTE_PkInfo
AS
(
    SELECT 
        t.name AS [TABLE_NAME],
        c.name AS [COLUMN_NAME],
        ty.name AS [DATA_TYPE],
        c.max_length AS [CHARACTER_MAXIMUM_LENGTH],
        IIF(c.max_length > 0 AND ty.name IN ('char', 'nchar', 'varchar', 'nvarchar'), 1, 0) AS [IsChar],
        ROW_NUMBER() OVER (ORDER BY c.column_id) AS [RN]
    FROM 
        sys.key_constraints kc
    INNER JOIN 
        sys.index_columns ic 
            ON kc.parent_object_id = ic.object_id 
           AND kc.unique_index_id = ic.index_id
    INNER JOIN 
        sys.columns c 
            ON ic.object_id = c.object_id 
           AND ic.column_id = c.column_id
    INNER JOIN 
        sys.tables t 
            ON kc.parent_object_id = t.object_id
    INNER JOIN 
        sys.types ty 
            ON c.user_type_id = ty.user_type_id
    WHERE 
        kc.type = 'PK'
        AND t.name = 'CustomerInvoices')
SELECT
    [WHERE].[CLAUSE]
FROM
(
    SELECT DISTINCT
        'DECLARE' AS [CLAUSE]
        , -40 AS [RN]
    UNION
    SELECT
        SPACE(3) + IIF(c2.[RN] = 1, '', ', ') + '@' + c2.[COLUMN_NAME]
        + ' ' + c2.[DATA_TYPE]
        + IIF(c2.[IsChar] = 1, '(' + CONVERT(VARCHAR, c2.[CHARACTER_MAXIMUM_LENGTH]) + ')', '')
        , -40 + c2.[RN]
    FROM
        [CTE_PkInfo] c2
    UNION
    SELECT DISTINCT
        '' AS [CLAUSE]
        , -30 AS [RN]
    UNION
    SELECT DISTINCT
        'SELECT' AS [CLAUSE]
        , -20 AS [RN]
    UNION
    SELECT DISTINCT
        SPACE(3) + '*' AS [CLAUSE]
        , -10 AS [RN]
    UNION
    SELECT DISTINCT
        'FROM' AS [CLAUSE]
        , -1 AS [RN]
    UNION
    SELECT DISTINCT
        SPACE(3) + QUOTENAME(c3.[TABLE_NAME]) AS T_SQL
        , 0 AS [RN]
    FROM
        [CTE_PkInfo] c3
    UNION
    SELECT
        IIF(c6.[RN] = 1, 'WHERE ' + CHAR(10) + SPACE(3) , SPACE(3) + 'AND ')
        
        + QUOTENAME(c6.[COLUMN_NAME]) + ' = ' +  '@' + c6.[COLUMN_NAME]
        , 100 + c6.[RN]
    FROM
        [CTE_PkInfo] c6
) [WHERE]
ORDER BY
    [WHERE].[RN];

DROP TABLE IF EXISTS [CustomerInvoices];