DZFunctions.txt

CREATE OR ALTER PROCEDURE GetTableFields @TableName NVARCHAR(128)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = '
    SELECT 
        c.TABLE_CATALOG AS DatabaseName,
        c.TABLE_SCHEMA AS SchemaName,
        c.TABLE_NAME AS TableName,
        c.COLUMN_NAME AS ColumnName,
        c.DATA_TYPE AS DataType,
        c.IS_NULLABLE AS IsNullable
    FROM INFORMATION_SCHEMA.COLUMNS c
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 
        ON c.TABLE_NAME = k.TABLE_NAME 
    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS con 
        ON k.CONSTRAINT_NAME = con.CONSTRAINT_NAME
    WHERE c.COLUMN_NAME IS NOT NULL 
        AND c.TABLE_NAME = ''' + @TableName + '''   
    GROUP BY c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE
    ORDER BY DatabaseName, SchemaName, TableName, ColumnName'

    EXEC sp_executesql @SQL
END
EXEC GetTableFields 'Producer'