drop ALL objects for a user

Question

Post reply

drop ALL objects for a user

Fredrik Vestin

SSC Veteran

Points: 286
More actions
August 8, 2006 at 8:44 am

#118908

Anyone know of a script or SP that deletes all objects in a specific database that is owned by a specific user? I mean all tables, indexes, sp, views etc.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Fredrik

Something like the script below will generate some SQL that you can inspect and then run against your database.

John

SET nocount ON

SELECT 'DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']'

FROM information_schema.tables

WHERE TABLE_SCHEMA = 'MyUser'

AND TABLE_TYPE = 'BASE TABLE'

SELECT 'DROP VIEW ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']'

FROM information_schema.tables

WHERE TABLE_SCHEMA = 'MyUser'

AND TABLE_TYPE = 'VIEW'

SELECT 'DROP PROCEDURE ' + ROUTINE_SCHEMA + '.[' + ROUTINE_NAME + ']'

FROM information_schema.routines

WHERE ROUTINE_SCHEMA = 'MyUser'

AND ROUTINE_TYPE = 'PROCEDURE'

SELECT 'DROP FUNCTION ' + ROUTINE_SCHEMA + '.[' + ROUTINE_NAME + ']'

FROM information_schema.routines

WHERE ROUTINE_SCHEMA = 'MyUser'

AND ROUTINE_TYPE = 'FUNCTION'

Fredrik Vestin SSC Veteran Points: 286 More actions · Answer 2

John,

Thanks for your reply. This will work, but since many tables have refrential integrity with other tables you simply cannot drop them in any order you like, but you have to drop the in a specific order.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

What's a problem?

Drop related FK's first.

_____________
Code for TallyGenerator

Lowell SSC Guru Points: 323518 More actions · Answer 4

here's a solution that creates the statements in FK hierarchy order by using one of the sp_ms procs;

note that I know it is not 100% perfect; if the database has circular dependancies, where TABLEA references B which references C which references A, the order will not be perfect; I have a different hierarchy method i wrote that i will hunt down if anyone needs it;

--for objects:

CREATE TABLE #HIERARCHY (OTYPE int, ONAME varchar(517), OWNER varchar(517), SEQ int)

INSERT #HIERARCHY

EXEC sp_msdependencies @intrans = 1

DELETE FROM #HIERARCHY WHERE ONAME NOT IN

(SELECT NAME FROM sysobjects WHERE sysobjects.xtype in ('U','V','P','FN','IF','TF'))

SELECT * FROM #HIERARCHY

SELECT

CASE

WHEN OTYPE=1 THEN 'DROP FUNCTION '

WHEN OTYPE=4 THEN 'DROP VIEW '

WHEN OTYPE=8 THEN 'DROP TABLE '

WHEN OTYPE=16 THEN 'DROP PROCEDURE '

END

+ owner +'.' + oname as SQLstatement

from #HIERARCHY WHERE OWNER <> 'dbo'

ORDER BY OWNER,SEQ DESC

results:

DROP VIEW webdev.VW_SFSETASD

DROP TABLE webdev.ACACTDET

DROP TABLE webdev.GMATDET

DROP TABLE webdev.GMATHEAD

...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Fredrik Vestin SSC Veteran Points: 286 More actions · Answer 5

Thanks a bunch everyone for your help. I will give this a shot!