delete tables,views,stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

delete tables,views,stored procedures

Author

Topic

Mageshkumar
Starting Member

29 Posts

Posted - 2008-09-02 : 05:57:53

how to delete a particular database all the tables, views and stored procedures?,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-02 : 06:05:18

Do backup of database before running this

DROP Database your_db

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 06:12:32

This will give you a start.

What about user defined datatypes?
What about synonyms?
What about linked servers?
What about database triggers?
What about assemblies?
What about rules?
What about defaults?
What about Service Broker information?
What about Full Text Catalogs?
What about partitioning functions?
What about users?

DECLARE	@SQL VARCHAR(8000)

WHILE EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW')
	BEGIN
		SELECT	TOP 1 
			@SQL = 'DROP VIEW ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
		FROM	INFORMATION_SCHEMA.TABLES
		WHERE	TABLE_TYPE = 'VIEW'

		EXEC	(@SQL)
	END

WHILE EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')
	BEGIN
		SELECT	TOP 1 
			@SQL = 'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
		FROM	INFORMATION_SCHEMA.TABLES
		WHERE	TABLE_TYPE = 'BASE TABLE'

		EXEC	(@SQL)
	END

WHILE EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE')
	BEGIN
		SELECT	TOP 1 
			@SQL = 'DROP PROCEDURE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
		FROM	INFORMATION_SCHEMA.ROUTINES
		WHERE	ROUTINE_TYPE = 'PROCEDURE'

		EXEC	(@SQL)
	END

WHILE EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION')
	BEGIN
		SELECT	TOP 1 
			@SQL = 'DROP FUNCTION ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
		FROM	INFORMATION_SCHEMA.ROUTINES
		WHERE	ROUTINE_TYPE = 'FUNCTION'

		EXEC	(@SQL)
	END

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources