A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Friday, June 8, 2012

Tables with a comma delimited list of columns

My coworker wants to auto-build code to construct some Columnstore indexes. Why not generally a sound practice, I though it'd be fun to write the basic script. It's a simple CTE to generate the source list of schema, tables and columns. I then use the FOR XML trick to create a comma delimited list in the UQ section and then with the glory that is the CONCAT function in 2012, we create a boatload of columnstore indexes

; WITH SRC AS
(
    -- Generate a list of all the tables and columns in a database
    SELECT
        QUOTENAME(SCHEMA_NAME(T.schema_id)) AS table_schema
    ,   QUOTENAME(T.name) AS table_name
    ,   QUOTENAME(SC.name) AS colum_name
    FROM
        sys.tables T
        INNER JOIN
            sys.columns SC
            ON SC.object_id = T.object_id
)
, UQ AS
(
-- Retrieve the unique list of schemas and tables
SELECT DISTINCT
    SRC.table_schema
,   SRC.table_name
    -- voodoo here for rollup
,   STUFF((SELECT ',' + CAST(R.colum_name AS varchar(50)) FROM SRC R WHERE R.table_name = SRC.table_name AND R.table_schema = SRC.table_schema FOR XML PATH('')), 1, 1, '') AS column_list
FROM 
        SRC
)
SELECT
    -- Generate the create statements for all the tables
    CONCAT('CREATE NONCLUSTERED COLUMNSTORE INDEX ', 'ixcs_', REPLACE(REPLACE(uq.table_name, ']', ''), '[', ''), ' ON ', UQ.table_schema, '.', UQ.table_name, '(', uq.column_list, ' )') 
FROM
    uq;

No comments: