Empty / Remove SQL Server Tables, Views, Store Procedure, Function


DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

create procedure usp_DropSPFunctionsViews
as
-- variable to object name
declare @name  varchar(1000)
-- variable to hold object type
declare @xtype varchar(20)
-- variable to hold sql string
declare @sqlstring nvarchar(4000)
declare SPViews_cursor cursor for

SELECT QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) AS name, ROUTINE_TYPE AS xtype
FROM
INFORMATION_SCHEMA.ROUTINES
UNION
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS name, 'VIEW' AS xtype
FROM
INFORMATION_SCHEMA.VIEWS
open SPViews_cursor
fetch next from SPViews_cursor into @name, @xtype
while @@fetch_status = 0
begin
-- test object type if it is a stored procedure
   if @xtype = 'PROCEDURE'
      begin
        set @sqlstring = 'drop procedure ' + @name
        exec sp_executesql @sqlstring
        set @sqlstring = ' '
      end
-- test object type if it is a function
   if @xtype = 'FUNCTION'
      begin
        set @sqlstring = 'drop FUNCTION ' + @name
        exec sp_executesql @sqlstring
        set @sqlstring = ' '
      end
-- test object type if it is a view
   if @xtype = 'VIEW'
      begin
         set @sqlstring = 'drop view ' + @name
         exec sp_executesql @sqlstring
         set @sqlstring = ' '
      end
-- get next record
    fetch next from SPViews_cursor into @name, @xtype
  end
close SPViews_cursor
deallocate SPViews_cursor
GO

Advertisements

JTDS


Interface jTDS Implementation
java.sql.Driver net.sourceforge.jtds.jdbc.Driver
javax.sql.DataSource net.sourceforge.jtds.jdbcx.JtdsDataSource
javax.sql.ConnectionPoolDataSource net.sourceforge.jtds.jdbcx.JtdsDataSource
javax.sql.XADataSource net.sourceforge.jtds.jdbcx.JtdsDataSource
javax.naming.spi.ObjectFactory net.sourceforge.jtds.jdbcx.JtdsObjectFactory

http://jtds.sourceforge.net/faq.html

Interface jTDS Implementation
java.sql.Driver net.sourceforge.jtds.jdbc.Driver
javax.sql.DataSource net.sourceforge.jtds.jdbcx.JtdsDataSource
javax.sql.ConnectionPoolDataSource net.sourceforge.jtds.jdbcx.JtdsDataSource
javax.sql.XADataSource net.sourceforge.jtds.jdbcx.JtdsDataSource
javax.naming.spi.ObjectFactory net.sourceforge.jtds.jdbcx.JtdsObjectFactory