How to drop all tables, all views, and all stored procedures from a SQL Server 2005 Database?

It may not be a hardcore requirement on day-to-day basis to drop all tables, views and stored procedures from a SQL Server database within your environment, but it will be handy to have such a code at your end when such task is required.

There are 2 ways to accomplish this, first using undocumented stored procedure such as 'sp_MSforeachtable' as follows:

exec sp_MSforeachtable "DROP TABLE ? PRINT '? to be dropped' "

Where the results will have all of the tables to be dropped, ok how about for views & stored procedure then. Here it goes:

create procedure Usp_DropAllSPViews
as

declare @name  varchar(100)
declare @xtype char(1)
declare @sqlstring nvarchar(1000)

declare AllSPViews_cursor cursor for
SELECT sysobjects.name, sysobjects.xtype
FROM sysobjects
  join sysusers on sysobjects.uid = sysusers.uid
where OBJECTPROPERTY(sysobjects.id, N'IsProcedure') = 1
  or OBJECTPROPERTY(sysobjects.id, N'IsView') = 1 and sysusers.name =
'USERNAME'

open AllSPViews_cursor

fetch next from SPViews_cursor into @name, @xtype

while @@fetch_status = 0
  begin
-- obtain object type if it is a stored procedure or view
   if @xtype = 'P'
      begin
        set @sqlstring = 'drop procedure ' + @name
        exec sp_executesql @sqlstring
        set @sqlstring = ' '
      end
-- obtain object type if it is a view or stored procedure
   if @xtype = 'V'
      begin
         set @sqlstring = 'drop view ' + @name
         exec sp_executesql @sqlstring
         set @sqlstring = ' '
      end

    fetch next from AllSPViews_cursor into @name, @xtype
  end

close AllSPViews_cursor
deallocate AllSPViews_cursor

T/SQL Code to remove SQL Injection Values from your tables

With SQL Injection Attacks being all the rage these days, I’ve been asked a couple of times for T/SQL code to clean up the database.

So I threw this code together to clean up the data. This code will clean all the character and uni-code columns in all the user defined tables in the system. You’ll need to be dbo or sysadmin to run this without error. If you have TEXT or NTEXT columns it will through an error for those columns. Cleaning TEXT and NTEXT columns is a little more complex as you can’t use the REPLACE function on a TEXT or NTEXT datatype.

DECLARE @sql NVARCHAR(4000) DECLARE @InsertedValue NVARCHAR(1000) SET @InsertedValue = 'The Script tags which were inserted' DECLARE cur CURSOR FOR    select 'update [' + sysusers.name + '].[' + sysobjects.name + ']     set [' + syscolumns.name + '] = replace([' + syscolumns.name + '], ''' + @InsertedValue + ''', '''')'    from syscolumns    join sysobjects on syscolumns.id = sysobjects.id     and sysobjects.xtype = 'U'    join sysusers on sysobjects.uid = sysusers.uid    where syscolumns.xtype in (35, 98, 99, 167, 175, 231, 239, 241, 231)   OPEN cur   FETCH NEXT FROM cur INTO @sql   WHILE @@FETCH_STATUS = 0   BEGIN    exec (@sql)    FETCH NEXT FROM cur INTO @sql   END   CLOSE cur   DEALLOCATE cur