x

Schema dependecnies

Hi,

I have schema's in my database which i think we are not using them anymore. We have 100's of them in each database. Before dropping them how can i check their dependencies, whether they are associated with any table/user or anything?

Any help would be much appreciated

more ▼

asked Aug 11 at 01:05 PM in Default

avatar image

rehaan
81 1 3 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You should be able to query that using some of the system tables:

 SELECT * FROM sys.schemas s
     LEFT JOIN sys.all_objects ao ON s.schema_id = ao.schema_id
     INNER JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
 WHERE ao.schema_ID IS NULL
     AND dp.type <> 'R' --roles
 GO

You might have to adjust for your particular environment but this would give you list of schema's that do not have any objects tied to it.

Hope that helps!

more ▼

answered Aug 11 at 01:52 PM

avatar image

JohnM
14.2k 3 7 14

Thanks John M

Aug 12 at 01:58 AM rehaan
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x43
x2

asked: Aug 11 at 01:05 PM

Seen: 26 times

Last Updated: Aug 12 at 01:58 AM

Copyright 2017 Redgate Software. Privacy Policy