; 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;
Pages
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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment