So what is a Schema?
A SQL Server database contains multiple objects: tables, views, stored procedures, functions, indexes, triggers.
SQL Server Schemas are a logical collection of database objects. A user that owns a schema in the database are known as Schema owners. There is no restrictions on the number of objects can be in a schema.
If you need to find that default schema for a user – you need to be logged into SSMS with that user and then run the following query:
If you need to retrieve all the schema and their owners in a database, use this SQL query:
SELECT s.name AS schema_name,
u.name AS schema_owner
FROM sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY s.name;
On some occasion, a business user will give me a sql script to run on a database that does not have a USE clause in the script and they have no idea on which database it needs to be run on. And in most cases there are 50+ databases on the sql instance.
Here is an example of a script:
Select * from schema_name.table_name
Now usually the schema name is dbo but in some rare occasions they have a unique name for the schema. Since the user does now know which database the sql script on I use msforeachdb to find the schema name in all the databases and then I can find out in which database the schema resides on and then I can run the script the business user gave me on that database and provide the user the information they requested.
Here is the sql script to find a schema in all databases:
--This query will return a listing of all tables that matches a schema name on a SQL instance:
DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT *
WHERE COLUMN_NAME LIKE "%prdobj%"'
EXEC sp_MSforeachdb @command
This has helped me tremendously in numerous occasion of tedious work on finding on which database the schema resides on. Hope this helps.