Fixed Price Projects          Flexibele IT oplossingen

Software op maat              Web Applications

You want to delete all tables and stored procedures (to include them in an installation script for example):

 

DECLARE @search nvarchar(50) = 'kang%'

 

-- foreign keys

SELECT  'ALTER TABLE ' + s.name+'.'+t.name + ' DROP CONSTRAINT [' + RTRIM(f.name) +'];' as statement

FROM sys.Tables t

INNER JOIN sys.foreign_keys f ON f.parent_object_id = t.object_id

INNER JOIN sys.schemas     s ON s.schema_id = f.schema_id

WHERE t.name like @search

 

UNION ALL

-- tables

SELECT ('IF Exists(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + table_name + ']'') AND type in (N''U'')) DROP TABLE ' + cast(table_name as varchar(255)) + ';' )

AS statement

FROM information_schema.tables

WHERE table_name LIKE @search

 

UNION ALL  

--stored procedures

SELECT ('IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + SPECIFIC_NAME + ']'') AND type in (N''P'', N''PC'')) DROP PROCEDURE [dbo].[' + SPECIFIC_NAME + '];')

AS statement

FROM INFORMATION_SCHEMA.ROUTINES

WHERE (SPECIFIC_NAME like @search) and ROUTINE_TYPE = 'PROCEDURE'

 

 

UNION ALL  

--functions

SELECT ('IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + SPECIFIC_NAME + ']'') AND type in (N''FN'', N''IF'', N''TF'', N''FS'', N''FT'')) DROP FUNCTION [dbo].[' + SPECIFIC_NAME + '];')

AS statement

FROM INFORMATION_SCHEMA.ROUTINES

WHERE (SPECIFIC_NAME like @search) and ROUTINE_TYPE = 'FUNCTION'

 


This will generate all drop statements for objects with an IF EXISTS test so the statements will not fail if an object does not exist

 

You can then copy paste the results in a new query window and execute them.

 

As you can not drop tables with foreign keys, you need to delete them first (1e part of the union)

Categorie: SQL
Actions: E-mail | Permalink |

waardering