search

LitKnd / littlekendracomments

1 stars 0 forks source link

Post: How to Script Out Indexes from SQL Server #11

Open LitKnd opened 3 years ago

LitKnd commented 3 years ago

Comment history imported from Wordpress

Aaron Bertrand May 5, 2016 8:06 am Nice Kendra, one suggestion, add QUOTENAME() around table/schema/index/column names. Otherwise people with poor naming conventions will complain that it breaks. 🙂

Loading... Reply Kendra Little May 5, 2016 4:26 pm Oh, that is very good advice. Those darned spaces and the like. I think I have all the names bracketed now, thank you!

Loading... Reply ross May 5, 2016 8:55 am Hi Kendra,

Thanks for the great script

I was getting some ‘duplicate’ rows where tables and indexes with the same name existed in different databases on the same server, presumably because sys.dm_db_index_usage_stats is scoped at server level, rather than database level (unlike the other sys tables referenced in the script)

Updating Line 68 with this additional criteria on DB_ID() removed the ambiguity:

LEFT JOIN sys.dm_db_index_usage_stats AS stat ON si.object_id=stat.object_id and si.index_id=stat.index_id AND stat.database_id = DB_ID()

Loading... Reply Kendra Little May 5, 2016 4:26 pm Awesome catch, thanks! I updated the script with that restriction.

Loading... Reply JP May 5, 2016 9:57 am Kendra, This is helpful to check also with different versions of database. Thank you,

Loading... Reply Marcy Ashley-Selleck May 5, 2016 1:59 pm This can also be done using the Generate Scripts feature in SSMS: right click on a database, click Tasks, click Generate Scripts
 and walk through the wizard, be sure on the Set Scripting Options click on the Advanced button and select Script Indexes=True to include the indexes. This an be done on all tables or specific tables of your choosing, and you can direct the output in a number of ways as well.

Loading... Reply Kendra Little May 5, 2016 4:04 pm Hi Marcy,

The output from Generate Scripts is not quite the same. Scripting from Generate Scripts will NOT contain any data compression settings that you’ve applied on the index, whether on individual partitions or the whole index. I’ve heard some people express frustration over that, which is why I tried to cover it in this TSQL approach. (If there’s some way to get it to generate that, I’d love to learn how!)

This script also doesn’t cover some things that it does by default in the Generate Scripts task. This TSQL doesn’t cover the table definition itself, pad index settings, etc. I could work that stuff in if there’s demand for it.

Kendra

Update – I have recently learned you can change the options for scripting from object explorer in SSMS to include scripting out things like compression. You just need to know to go into your options and do it.

Loading... Reply Stephen A May 5, 2016 4:55 pm Maybe, as you’ve already pulled the FILLFACTOR, you might be so kind as to include it in the ALTER/CREATE when not in (0,100). With nearly 500 of over 4700 indexes to edit, if I had time



Thanks for the hard work putting this into a very usable format that Excel had no issues with!

Loading... Reply Kendra Little May 5, 2016 5:14 pm I like that idea! I’ll work on that.

Loading... Reply Stephen A March 8, 2017 10:24 am Although the formatting is messed up by posting here, does this work for you re adding FILLFACTOR to the WITH?

/ with clause – fillfactor and compression go here / CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL THEN N’ WITH (‘–

Loading... Reply Script Those Indexes – Curated SQL May 9, 2016 5:11 am [
] Kendra Little provides a T-SQL script to script out all indexes on a database: [
]

Loading... Reply Nicolas Reid May 9, 2016 8:56 am This is my version of almost the same, except it’s just for giving an overview instead of create statements. It helps me with keeping an overview over the 90 thousand databases, the oldest from 2002.

One nice aspect is it shows the actual column and include definition, including ascending or descending. You did know that Sql Server is perfectly happy to allow you to create a duplicate index, didn’t you?

One not so nice thing is that Sql Server is not able to tell you the create date of an index.(Believe me, it really, really can’t!)

select st.name as [table], si.name as [index], si.type_desc, nullif((iif(iif(isnull(ddius.last_user_seek,-1) >= isnull(ddius.last_user_scan,-1), isnull(ddius.last_user_seek,-1), isnull(ddius.last_user_scan,-1)) >= isnull(ddius.last_user_lookup,-1), iif(isnull(ddius.last_user_seek,-1) >= isnull(ddius.last_user_scan,-1), isnull(ddius.last_user_seek,-1), isnull(ddius.last_user_scan,-1)), isnull(ddius.last_user_lookup,-1))), -1) as [last_user_usage], ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates, si.is_primary_key, si.is_unique, si.is_unique_constraint, si.ignore_dup_key, si.has_filter, si.fill_factor, si.allow_row_locks, si.allow_page_locks, si.is_disabled, stuff((select ‘,’ + sc.name + case when sic.is_descending_key=1 then N’↓’ else N” end from sys.index_columns sic join sys.columns sc on sc.object_id=sic.object_id and sc.column_id=sic.column_id where sic.object_id=si.object_id and sic.index_id=si.index_id and sic.is_included_column=0 order by sic.is_included_column,sic.key_ordinal for xml path(”)),1,1,”) as [index_columns], isnull(stuff((select ‘,’ + sc.name from sys.index_columns sic join sys.columns sc on sc.object_id=sic.object_id and sc.column_id=sic.column_id where sic.object_id=si.object_id and sic.index_id=si.index_id and sic.is_included_column=1 order by sic.is_included_column,sic.key_ordinal for xml path(”)),1,1,”),”) as [index_included_columns] from sys.indexes si join sys.tables st on st.object_id=si.object_id left outer join sys.dm_db_index_usage_stats ddius on si.object_id = ddius.object_id and si.index_id = ddius.index_id and ddius.database_id = db_id() order by st.name,si.name

Loading... Reply Kendra Little May 9, 2016 2:55 pm “One nice aspect is it shows the actual column and include definition, including ascending or descending. You did know that Sql Server is perfectly happy to allow you to create a duplicate index, didn’t you?”

Yep
 SQL Server would even suggest duplicate indexes in the Missing Indexes Feature up until SQL Server 2012. I filed a bug on that back in 2009 that was fixed. But of course you can still create them.

I did have the script show DESC columns (ASC is the default), I’m not clear on whether you didn’t look at the script or if you’re saying there’s a bug in the script?

Loading... Reply A couple of interesting posts from other bloggers | High Altitude SQL: A 9,000' view of Microsoft SQL Server and Access May 9, 2016 10:27 am [
] How to Script Out Indexes from SQL Server [
]

Loading... Reply sqlproddba August 13, 2016 1:31 am Great script Kendra! Adding other index types and with parameter options in index_create_statement would make it complete.

Loading... Reply sqlproddba August 15, 2016 8:10 am Hello Kendra,

I am using this script in my current project to backup CL and NC indexes. I want to run it for all DBs in just one go. I tried few ways like using cursor, msforeachdb, and temp table but did not succeed. Do you have an easy way to do this?

Loading... Reply Nick August 15, 2016 2:57 pm Please tell how your attempts didn’t succeed. I do the same using Linked Servers (I have a multi-server situation). In the outer script-loop I first create a central discretely named results table in my personal administrator database to catch the results in, then I build dynamic SQL-strings and then “EXEC(@SQLscript) AT MyLinkedServer”. These SQL-strings are built to insert their results into the central table. My script loops through all databases registered in our central database, where all databases are registered at which server they reside and their name DB_NAME. I have name d my script the ‘BrickLoop’, since it loops over all indicated homogeneous databases over all our servers (bricks I call them). It needs some investment, but it works a wonder since I devised it over 12 years ago.

The essential tricks are:

  1. first create a centrally located catching table for all results
  2. build dynamic SQL-strings in a loop
  3. loop over your databases with “EXEC @SqlString AT My LinkedServer” (the @SqlString must insert into the central table, obviously)
  4. regard your results in the central table
  5. Voila!

TIP: use “RAISERROR(@Msg,0,1) WITH NOWAIT” to force verbose progress output during the loop over all databases.

Often my BrickLoop takes several hours to complete for our 190.000 databases, but then I do have complete and concise results.

Loading... Reply Kendra Little August 15, 2016 4:23 pm Did you say 190,000 databases? Just curious– how many instances do you have them spread across, and how long is SQL Server startup time on the instances that have the most databases?

(Not asking related to the current topic, just always fascinated by instances with lots of databases.)

Loading... Reply Arjan Fraaij September 6, 2016 12:07 am Hi Kendra,

Very nice it only also creates an “CREATE UNIQUE NONCLUSTERED INDEX” record for indexes with is_unique_constraint = 1.

Should it for those not create a “ADD CONSTRAINT [] UNIQUE NONCLUSTERED” record?

Still very nice you helpt me a lot


Loading... Reply Kendra Little September 7, 2016 6:55 am Great catch! I can’t fix that immediately, but I’ll put that on the list to correct soon.

Loading... Reply Brenda September 21, 2016 11:50 am Great script. Just wondering if you add a IF NOT EXISTS? Also maybe a companion script that does a DROP?

Loading... Reply John McCormack November 9, 2016 5:21 am Thank you Kendra.

I had a problem that someone had dropped some indexes from dev.

I ran this script in dev and production and copied the outputs of the index_create_statement columns from both into txt files. I then loaded these into tables and ran a query to identify what was missing from dev compared to prod.

SELECT index_create_statement as missing_dev FROM prod_indexes EXCEPT SELECT * FROM dev_indexes

It saved me a lot of work.

Thanks

Loading... Reply dba100 January 3, 2017 11:22 pm hi,

but any post you all tried, the most accurate one, shows the duplicated index by index column and missing index?

Loading... Reply Kendra Little January 4, 2017 8:59 am I’m not sure what you mean by your question– could you perhaps restate it?

Loading... Reply dba100 January 4, 2017 7:15 pm sorry, I think there are some typo here. I mean this kind of script is good and I am not sure if you can share script to find out unused index and missing index, the script is perfect if it also generate the respective create and drop statement for the indexes.

Loading... Reply Milla April 19, 2017 9:54 am Thank you for a great script.

Loading... Reply Xian Wang June 2, 2017 11:00 am Hi Kendra,

Thank you for a great script. Any chance this could be modified to included columnstore indexes as well?

Loading...

Reply Kendra Little June 6, 2017 11:31 am I would love to! It’s on my list.

Loading...

Reply MH December 4, 2017 11:56 am Thank you very much for sharing the script! Like Xian, I would be very interested in using this with columnstores as well. Any chance you have added that to your script?

Loading...

Reply Paul November 1, 2017 10:42 am Super Script..excellent
really same my time.

Loading... Reply Adrian Buckman March 8, 2018 2:06 am Thanks for sharing Kendra, I found this super useful the other day – with a small tweak I used it to script out missing clustered indexes on a bunch of databases where they had been upgraded through versions but clustered indexes had been skipped, I know you can use the GUI etc but where this shined was being able to loop through every database and compare against a base schema – was awesome!

Loading... Reply Query to Script out all Indexes in SQL Server Database: – Always SQL with Kundan Dasange June 7, 2018 2:57 pm [
] TSQL For Scripting Out All Indexes in a Database [
]

Loading... Reply Andrzej December 6, 2018 1:33 am Hi I would add AND t.is_ms_shipped = 0 on line 157. I don’t think anyone needs to script out Microsoft generated scripts.

Loading... Reply Kendra Little December 10, 2018 9:08 am The query has a full join to sys.tables, which only creates user tables, so this shouldn’t be an issue.

Loading... Reply Andrzej November 14, 2019 8:22 am As I noticed replication creates some internal tables which are marked is_ms_shipped = 1, That’s why I made the previous comment. Almost year ago 🙂

Loading... Reply Raghu February 4, 2019 8:04 am Thank you for a great script!

Loading... Reply Gintaras January 24, 2020 9:52 am If there’s an index to support a unique constraint, then this script would only recreate the index, but not the UQ constraint. The script would be more beautiful if it would script out the UQ constraint (same way as PK) 🙂

jason-uithol-aurizon commented 2 years ago

If I import a bacpac exported from Azure into SQL-Server, run a DDL script on it that modifies data, schema and indexes, then run this query on that modified database, I get this error

Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in add operator occurring in SELECT statement column 5.

All databases are SQL_Latin1_General_CP1_CI_AS, as well as the host instance. Latin1_General_100_CI_AS_KS_WS_SC is the "default catalog collation" which I read as being for the schema metadata and so forth. Which makes sense, that's what you are querying.

I cannot for the life of me fix this.

UPDATE: I fixed it. Change this line:

N'ALTER TABLE ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(si.name) + N' PRIMARY KEY ' +

To this:

N'ALTER TABLE ' + QUOTENAME(sc.name) collate Latin1_General_100_CI_AS_KS_WS_SC + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(si.name) + N' PRIMARY KEY ' +