Home > Sql Server > Script Table Index in SSMS Using Powershell

Script Table Index in SSMS Using Powershell

Most important part of DBA’s job is to regurlarly tune db queries .When you are tuning queries you want to know indexes created on table with column names etc . This tip is all about getting that information from DB quickly.

There are several ways to get this information. You can expand table in ssms and script indexes manually which becomes tedious locating table if you have thousands of tables ūüôā as in my case or you can create your own sp querying system tables and set it up as shortcut key which works great but as I have been playing with powershell lately so I tried to do same using powershell. But there is one small caveat. If you want to run powershell script from sql server you will have to use xp_cmdshell command. xp_cmdshell is disabled by default so you may need to enable it first by running below command :

Exec sp_configure ‘xp_cmdshell‘ , 1;

go

Reconfigure with override

go

I created below powershell script and saved it on server and saved it as script_indexes.ps1 .

param([string]$Server,[string]$database,[string]$table) ¬†[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null ¬†$srv =new-object Microsoft.SqlServer.Management.Smo.Server($Server) ¬†$db = $srv.databases[$database] ¬†$tb = $db.tables | where-object {$_.Name -like $table} ¬† foreach ($index in $tb.Indexes) ¬†¬† { ¬†¬†¬†¬† $index.script() + “GO `r`n ” ¬†¬† }

Finally create SP in DB which calls this powershell script and output index script in result window of SSMS.. For me this has been really fast way to see columns in index including included columns. Also it is easy if you want to modify indexes as you already have create index script ready. Here is sp code.

/***********************************************************

Sample Call — Exec nsp_script_indexes ‘TableName

************************************************************/

CREATE PROCEDURE nsp_Script_Indexes

@TbName sysname

AS

BEGIN

SET NOCOUNT ON;

DECLARE @cmd AS NVARCHAR (500);

DECLARE @Servername AS sysname,@DbName AS sysname;

— Temp table to store powershell command output.

CREATE TABLE #output

(

id           INT           IDENTITY,

Index_Script VARCHAR (MAX)

);

SET @Servername = @@SERVERNAME;

SET @DbName = DB_NAME();

—Change path for powershell file location for your server.

SET @cmd = ‘PowerShell.exe “G:\PowerShell\script_indexes.ps1 ‘ + ‘”‘ + @Servername + ‘“‘ + ‘ “‘ + @DbName + ‘“‘ + ‘ “‘ + @TbName + ‘“‘ + ‘”‘;

–Print @cmd

INSERT INTO #output (Index_Script)

EXECUTE xp_cmdshell @cmd;

–Delete unnecessary data from temp table.

DELETE #output

WHERE  Index_Script IS NULL

OR Index_Script = ”;

–Use ‘GO’ word in script to find end of each create index.

SELECT row_number() OVER ( ORDER BY ID) AS rn,

ID,

Index_Script

INTO   #opt

FROM   #output

WHERE¬† ltrim(rtrim(Index_Script)) = ‘GO‘;

DECLARE @i AS INT, @c AS INT, @ind AS NVARCHAR (1000), @d AS INT, @n AS INT;

SET @d = 0;

–Finally concatenate index script back and print.

SELECT @i = MIN(rn),

@c = max(rn)

FROM   #opt;

WHILE (@i <= @c)

BEGIN

SET @n = @i;

SELECT @n = ID

FROM   #opt

WHERE  rn = @i;

SELECT @ind = (SELECT CONVERT (VARCHAR (1000), Index_Script + ”)

FROM   #output

WHERE  id < @n

AND id > @d

FOR¬†¬†¬† XML PATH (”));

SET @d = @n;

SET @i = @i + 1;

PRINT @ind + char(10) + ‘GO‘;

END

END

Now you can add this SP call as an shortcut key in SSMS and you are ready to quickly get index info while troubleshooting queries.

Sample Call —¬† Exec nsp_script_indexes ‘TableName’

Happy tuning..

-NJ

Advertisements
Categories: Sql Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: