Rob Farley posted a
very interesting codegen post on scripting objects this morning, and as a reforming codegen junky I just couldn't let it go without comment
Firstly, I modified his query as so:
select quotename(si.name) as "@IndexName", quotename(ss.name) AS "@SchemaName", quotename(so.name) AS "@ObjectName",
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 "@IndexColumns"
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
for xml path('index'), root('indexes')
Things I'll point out:
- Adding the extra for xml clause, this time specifying a better row element name than 'row' and also adding a document node
- Once the outter for xml clause is in place, we can alias the columns using @ to have them come out as elements in the results
We can feed the results of that query straight into the following XSLT:
<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match='/'>
<xsl:apply-templates select ='indexes/index'/>
</xsl:template>
<xsl:template match='index' >
create index <xsl:value-of select='@IndexName'/>
on <xsl:value-of select='@SchemaName'/>.<xsl:value-of select='@ObjectName'/> (<xsl:value-of select='@IndexColumns'/>)
</xsl:template>
</xsl:stylesheet>
I'm not claiming this method is superior, just different, which I think is in keeping with the spirit of Rob's post and I have learned more about the improvements of the for xml clause in SQL Server 2005 in the process.
I'll leave final judgment on the utility of this approach as an exercise to the reader; my instinctive reaction is to include it as a build step to help you snapshot schema changes between check-ins, or as Rob suggests to take objects from one database and create slightly different objects in another database – there are no wrong answers and if you think of something interesting please leave a comment
Grab the source files here:
ScriptObjects.zip (.78 KB)