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.