Scripting database objects a different way

Sometimes you want to make changes to your SQL Server objects (like tables, indexes, whatever), and you figure that it will be worth recreating them with a different set of options. One way of doing this is to generate a script, and then use a text manipulator to change the script in a way that will make it just what you want.

But I quite like querying the object catalog views – you know, those tables like sys.objects. Here’s an example of a script which will generate a create statement for each of the clustered indexes on user-tables in my database. Except that I’ve left out the word ‘clustered’, because when I run this on my new database, I don’t want these indexes to be clustered any more. Don’t ask me why, it’s just an example. You will notice that I have left out other things like the fill factor, etc… that’s just because this is JUST AN EXAMPLE. I have left “,*” in the select statement too, because it’s worth being aware of what other columns you have at your disposal here.

select ‘create index ‘ + quotename(si.name) + ‘ on ‘ + quotename(ss.name) + ‘.’ + quotename(so.name) + ‘ (‘ +
    stuff((select ‘,’ + quotename(sc.name)
    from sys.index_columns sic
        join
        sys.columns sc
            on sc.column_id = sic.column_id
    where so.object_id = sic.object_id
    and sic.index_id = si.index_id
    and sc.object_id = so.object_id
    order by sic.key_ordinal
    for xml path(”)
    ),1,1,”) + ‘);’ as cmd
,*
from sys.indexes si
    join
    sys.objects so
        on so.object_id = si.object_id
    join
    sys.schemas ss
        on ss.schema_id = so.schema_id
where so.type = ‘U’
and si.type = 1

I should point out that I’m using the FOR XML PATH(”) method to concatenate my list of columns. This is something that’s definitely worth knowing about if you intend to do this. I could’ve done something similar with sys.columns to create a script to generate CREATE TABLE statements too.