Returns the database schema name for schema-scoped objects. For a list of schema-scoped objects, see sys.objects (Transact-SQL).
Returns NULL on error or if a caller does not have permission to view the object. If the target database has the AUTO_CLOSE option set to ON, the function will open the database.
A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECT_SCHEMA_NAME may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.
The result set returned by this system function uses the collation of the current database.
If database_id is not specified, the SQL Server Database Engine assumes that object_id is in the context of the current database. A query that references an object_id in another database returns NULL or incorrect results. For example, in the following query the context of the current database is AdventureWorks2008R2. The Database Engine tries to return an object schema name for the specified object ID in that database instead of the database specified in the FROM clause of the query. Therefore, incorrect information is returned.
The following example specifies the database ID for the master database in the OBJECT_SCHEMA_NAME function and returns the correct results.
A. Returning the object schema name and object name
The following example returns the object schema name, object name, and SQL text for all cached query plans that are not ad hoc or prepared statements.
SELECT DB_NAME(st.dbid) AS database_name, OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name, OBJECT_NAME(st.objectid, st.dbid) AS object_name, st.text AS query_statement FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE st.objectid IS NOT NULL; GO
B. Returning three-part object names
The following example returns the database, schema, and object name along with all other columns in the sys.dm_db_index_operational_stats dynamic management view for all objects in all databases.