; 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;
A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.
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)
Labels
#TSQL2sDay
(3)
.NET
(1)
ADO.NET provider
(1)
asp.net
(1)
benchmark
(1)
Bingo
(2)
Bot detector
(1)
build events
(1)
C#
(10)
CTE
(6)
cv
(1)
datawarehouse modeling
(1)
deadlock
(1)
Denali
(3)
dtutil
(2)
Engine of the Devil
(3)
Excel
(1)
EXECUTE AS
(1)
Execute SQL Task
(1)
EzAPI
(7)
F#
(3)
facebook
(1)
html
(1)
identity theft
(1)
itms
(1)
linked servers
(1)
Macbook Pro
(1)
Macros
(2)
meme monday
(4)
Merge Join
(1)
MS SQL Server
(34)
MySQL
(2)
n00b
(1)
Parameters
(1)
parsing
(2)
permissions
(2)
powershell
(5)
presentation
(1)
Profiler
(1)
Project Euler
(2)
python
(1)
Ranking
(1)
Resume
(1)
RSClientPrint
(2)
schema
(1)
shameless self promotion
(1)
SQL Lock In
(1)
SQL PASS
(1)
SQL Saturday
(3)
SQL Saturday 53
(8)
SQL Saturday 91
(1)
SQL Server 2005
(22)
SQL Server 2008
(13)
SSAS
(1)
SSIS
(31)
SSISUploader
(1)
SSISUploader SSIS
(3)
SSMS
(1)
SSRS
(2)
standards
(1)
stupid
(1)
Summit 2009
(2)
Tofslie
(1)
troubleshooting
(1)
TSQL
(26)
Twitter
(2)
UAC
(1)
Visual Studio
(3)
Visual Studio 2010
(1)
Windows Server 2008 R2
(1)
XML
(1)
yahoo
(1)
No comments:
Post a Comment