June 24, 2013 by Kenneth Fisher
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
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.