Find out default SQL Server schema for session

Question

I have a requirement to know what the current default schema is in a SQL script that is doing some DDL. I don't need to set the schema, but I do need to get a reference to it (name or ID) into a variable. The script may be running as a windows login, so the following isn't sufficient:

SELECT name, default_schema_name  FROM sys.database_principals  WHERE type = 'S' and name = SYSTEM_USER --SYSTEM User won't be named as a principal 

I've thought of doing it by creating a randomly named object in the current schema, and then looking at its details in the information_schema, but does anyone have a tidier way?

I'm working in SQL Server 2005.

Answer 1

How about this.

SELECT SCHEMA_NAME()

http://msdn.microsoft.com/en-us/library/ms175068.aspx

SCHEMA_NAME will return the name of the default schema of the caller

Alternatively SCHEMA_ID()

Answer 2

How about using DATABASE_PRINCIPAL_ID to get the db principal of the current user?

SELECT name, default_schema_name  FROM sys.database_principals  WHERE type = 'S' and name = USER_NAME(DATABASE_PRINCIPAL_ID())