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)