SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Metadata Functions (OBJECT_NAME for example)

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.


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


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...