CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Bill
Bill

Reputation: 13

Listing all columns in SQL Server 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

Upvotes: 0

Views: 102

Answers (2)

ASH
ASH

Reputation: 20302

This will find all field names in all tables in your DB.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;

Upvotes: 0

S3S
S3S

Reputation: 25112

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

Upvotes: 2

Related Questions