Hi,
It seems that you maintain at least some tables in the dbo schema, but does your system runs on dbo schema? This is the first thing you should look at.
The following queries might help you. To run then, first execute the following statement;
USE [S11];
GO
1) The following query will give you the list of the users in your database;
SELECT
uid, status, name
FROM
sys.sysusers
WHERE
uid < 16384;
2) This query will list you all the schemas in your database:
SELECT
*
FROM
sys.schemas
WHERE
schema_id < 16384;
3) This query will give you the number of objects present in each schema in your database:
SELECT
count(schema_id) as #objects
FROM
sys.objects
GROUP BY
schema_id;
Now you know:
1) What users does exists in your Database;
2) What schemas does exist and to which user they belongs
3) The number of objects present in each schema.
Now all you need is to know which user tables are on 'dbo' schema and decide what to do with them.
1) All tables that belongs to dbo schema:
SELECT
*
FROM
sys.objects
WHERE
type = 'U'
AND schema_id = 1; --dbo
2) All tables in the dbo schema that also does exist in the 'sid' schema:
SELECT name FROM sys.objects WHERE type = 'U' and schema_id = 1 --dbo
INTERSECT
SELECT name FROM sys.objects WHERE type = 'U' and schema_id = ? --change to the schema_id of your SID database.
For example:
If the objects in the dbo schema are named like 'sap_' they probably belongs to a monitoring (Solution Manager) or are duplicated with the 'sid' schema tables. You should drop them.
If you have doubt please share the results of those queries, we might be able to further help you.
Best Regards,
Luis Darui
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.