Bill Bill - 4 months ago 6
SQL Question

Listing all columns in SQL tables

I need to list all DISTINCT column names for a list of tables in a Pivot format. I need the column headers to be the actual column names so that I can see which tables are missing the column. In the example below, using the first 2 tables, the ChnlngAssess_PTR should have the Question Attempt column (Column 6) as blank.

***table_name RespID LTID UserID ProjectID QuestionAttempt QType Question***
ARTWBT_PTR RespID LTID UserID ProjectID QuestionAttempt QType Question
ChlngAssess_PTR RespID LTID UserID ProjectID QType Question

Answer

Replace the AdventureWorksDW2012 with what ever your DB name is and if you want to limit the tables, put a WHERE clause in the first select on the t.TABLE_NAME

IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL DROP TABLE #tempTable

SELECT 
    t.TABLE_NAME,
    c.COLUMN_NAME
INTO #tempTable
FROM 
    AdventureWorksDW2012.INFORMATION_SCHEMA.TABLES t
    inner join
    AdventureWorksDW2012.INFORMATION_SCHEMA.COLUMNS c on c.TABLE_NAME = t.TABLE_NAME


DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)


SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(COLUMN_NAME)
FROM (SELECT DISTINCT COLUMN_NAME FROM #tempTable) AS COLUMN_NAME


SET @DynamicPivotQuery = 
  N'SELECT TABLE_NAME, ' + @ColumnName + '
    FROM #tempTable
    PIVOT(Max(COLUMN_NAME) 
          FOR COLUMN_NAME IN (' + @ColumnName + ')) AS PVTTable'

EXEC sp_executesql @DynamicPivotQuery
Comments