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, s.schema_id, 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 * FROM INFORMATION_SCHEMA.COLUMNS 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.