Tuesday, April 1, 2008

T-SQL Drop Stored Procs - CodeSmith version

And finally Wayne's Codesmith version for all procs prefaced with 'aspnet_':

<%@ CodeTemplate Language="C#" TargetLanguage="C#"  %>
<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Assembly Name="System.Design" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
USE [<%=SourceDatabase.Name%>]

GO

<%

foreach(CommandSchema command in SourceDatabase.Commands)
{
 %>
 
 <%
 if ((command.Name.Length>7) && (command.Name.Substring(0,7) == "aspnet_"))
 {
%>
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<%=command.Name%>]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[<%=command.Name%>]

GO

<%
 }
}
%>

No comments:

Post a Comment