What is a metadata function? According to BOL a metadata function is one that returns data about the database or its objects. Why am I mentioning metadata functions? Because they save me a boatload of effort! I love the system views, functions etc. I’ve made extensive use of them during my entire time as a DBA. Even way back when, when I was working in Foxpro. However I do get a little tired of tying in the sys.objects every time I need the name of an object. For example:

SELECT Users.name AS User_Name, Perm.permission_name,
Schemas.name AS Schema_Name, Obj.name AS Object_Name
FROM sys.database_permissions Perm
JOIN sys.database_principals Users
ON Perm.grantee_principal_id = Users.principal_id
JOIN sys.all_objects Obj
ON Perm.major_id = Obj.object_id
JOIN sys.schemas [Schemas]
ON Obj.schema_id = [Schemas].schema_id
WHERE perm.major_id <> 0

Now try it with the metadata functions OBJECT_NAME and OBJECT_SCHEMA_NAME.

SELECT Users.name AS User_Name, Perm.permission_name,
OBJECT_SCHEMA_NAME(major_id) AS Schema_Name, 
OBJECT_NAME(major_id) AS Object_Name
FROM sys.database_permissions Perm
JOIN sys.database_principals Users
ON Perm.grantee_principal_id = Users.principal_id
WHERE perm.major_id <> 0

Isn’t that much more concise? And for those of you who worry that functions on your queries will slow you down, this particular example run on one of my master databases ran 300 ms quicker and used 43 less reads when I used the functions.

Now let’s say I need to know if ANSI_NULLS is turned on for a SP. There really is only one solution.

SELECT OBJECTPROPERTYEX(object_id, 'IsAnsiNullsOn'), 
OBJECT_SCHEMA_NAME(object_id), name 
FROM sys.procedures

The upshot is that if you use the system views, functions etc I highly recommend that you take a look at these highly useful functions. They will not only save you time but can provide a great deal of information. If you want a good starting point here are a few of my favorites.

ColumnProperty
DatabasePropertyEx
DB_ID
DB_Name
Object_Name
Object_Schema_Name
ObjectProperty
ObjectPropertyEx

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, system functions, T-SQL