How can I generate a T-SQL script for just the indexes?

discount furniture » How can I generate a T-SQL script for just the indexes?

Thu, 28 Aug 2008 00:45:12 GMT

PingBack from http://informationsfunnywallpaper.cn/?p=2594

re: How can I generate a T-SQL script for just the indexes?

Stuntbeaver — Thu, 28 Aug 2008 15:39:31 GMT

The libraries you need to reference are a little different from the namespaces (at least they were for me):

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.SMO

Also I noticed that "so.AppendToFile = false" should be set to true.

Without this, it will only store script for the very last index (typically a primary key) in the text file. You will of course then need to make sure you truncate the text file at the start of the program.

re: How can I generate a T-SQL script for just the indexes?

Stuntbeaver — Fri, 29 Aug 2008 11:16:16 GMT

Forgot to say thanks for posting the example - I found the code quite handy.

re: How can I generate a T-SQL script for just the indexes?

moff — Thu, 04 Sep 2008 20:35:22 GMT

You can do this in one line with the new Powershell provider in SQL2008 (even if running against 2005) :-)

sl SQLSERVER:\SQL\<server>\<instance>\Databases\<db>\Tables;gci|%{gci ($_.DisplayName + '\Indexes')|%{$_.script()}} > c:\indexes.txt

Fair enough it's not very readable, and it doesn't include the exact scriptingoptions, but hey, it's one line ;-)

For reference:

sl = set-location

gci = get-childitem

% = foreach-object

re: How can I generate a T-SQL script for just the indexes?

benjones — Sun, 21 Sep 2008 16:22:42 GMT

Thanks for all the tips, that's useful info.