Error dropping shema

Attempting to drop shema then user via MSSQL 2005 Server Mngr but get the following error.
I do know this user account is not logged in when this error occurs.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot drop schema 'EPIDIRECT\fmg.Jackie.Infusino' because it is being referenced by object 'ds_GetServerCase'.
(Microsoft SQL Server, Error: 3729)

Any suggestion how to determine dependency causing this problem and how to correct?
JoeSnyderJrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Faiga DiegelSr Database EngineerCommented:
Look for this object: EPIDIRECT\fmg.Jackie.Infusino.ds_GetServerCase. This maybe an SP or table or view or function. Change that object to another owner -- dbo perhaps, then try droppign the schema again.
bashka_abdyliCommented:
Right click the schema and thru Studio Management and click View Dependecies.
JoeSnyderJrAuthor Commented:
ds_GetServerCase is owned by dbo with no dependencies

Right clicking on this schema does not show 'View Dependencies' as an option.

schema owner is dbo
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

bashka_abdyliCommented:
This query will generate script to change schema name for all the tables:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name FROM sys.Tables p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = 'oldschemaname'

this second is for stored procedures:

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = 'oldschemaname'

First you will alter all these objects and after this delete the schema.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoeSnyderJrAuthor Commented:
Ran this script, then ran following script. All dependences seem to still exist. Attempted schema delete with same error

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name <> 'dbo'

SELECT s.Name + '.' + p.Name, * FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name <> 'dbo'

Not sure if could be relevant but each of the users are also members of a group with dbo priviledges.
bashka_abdyliCommented:
Execute the scripts that the previus query will generate. So that all the procedures will change the schema to dbo.
JoeSnyderJrAuthor Commented:
I did run these scripts and attempted to delete the schema but continued to receive the dependency error.
However I did find where an application was creating a stored procedure for each login account. Was able to delete these procedures and then successfully remove the schemas in question.

Given this suggestion helped me to find problem but not convinced it fixed I am awarding partial points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.