$$SWYNK$$
SSWUGtv
With Stephen Wynkoop
When I go to linkedin and review the profiles online I can’t think of any that has not been open to new opportunities. I haven’t looked at it scientifically, but my gut feeling says it would be rare for an individual to have that turned off. In today’s show Steve interviews Laura Rose with the question, "Why do employees continue to job-search?" Employers and Employees will find this interview helpful learning how to make use of available job openings.
Watch the Show
SQL Tip of the Day
One of the most popular and frequently blogged un-documented stored procedures is MSDB.dbo.SP_MSForEachDB. Generally, I don’t use un-documented code, especially in a production system. However, this stored procedure is so functional I am not surprised every time I see it arise on another new blog. It is just that helpful.
Just today I had a developer come to me asking to find out everywhere a table or column was used. I like to use sp_depends for this. However, sp_depends is not always accurate. It cannot create a dependency if objects are created in a reverse order. Say spA calls SPb…it will compile even if SPb does not exist. It will also execute if SPb is created before it is executed. However, the relationship between the two stored procedures will not be tracked, nor will it show up using sp_depends.
Another reason dependencies in SQL Server is not always accurate is that it does not support heterogeneous commands. A stored procedure in database a calling a stored procedure in database b will not track any dependency. Even the latest dependency tracking strategies in SQL Server will not support this kind of implementation. In some environments, heterogeneous database work is necessary.
There are some system tables that will assist you in finding these kinds of dependencies. INFORMATION_SCHEMA views reveal a lot of data. A worst case scenario may also be used with syscomments. The syscomments view, which is retained only for backward compatibility, has been deprecated for some time. However, it has a text column which contains text for all non-encrypted stored procedures, triggers, view definitions, functions, etc.
The following query using MSDB.DBO.SP_MSForEachDB will find anything in the text field of syscomments and tell you which database and object the search text is found.
EXEC MSDB.dbo.SP_MSFOREACHDB
'
SELECT ''?'' AS DBName
,SO.Name
FROM [?].dbo.syscomments sc
JOIN [?].dbo.sysobjects so ON SO.ID = SC.ID
WHERE sc.text LIKE ''%Searchtext%''
'
This is not the most elegant code. It uses system views that are not guaranteed to work. However, it is the most completely flexible technique for finding dependencies without writing some recursive code in PowerShell or some other tool using SQL.DMO.
When I just need something fast, this is a great way to find it. When it goes away, I’ll probably figure out a way to create my own SP_MSForEachDB.
Another great way to find dependencies is to maintain a complete script of your databases in your version control. This is a great practice anyway. The advantage in this case it the ability to do a free text search through your source tree in both database code and application code. Now you have a complete view of consumers for database objects.
So, that is my little tip for the day. Nothing special or original…just a very useful little tool. If you didn’t know about it, it can be useful. If you did, I guess I need to get in line behind the other gazillion blogs before me.
So, would you like to contribute a tip for the day? Drop me a note with your tip to btaylor@sswug.org.
Cheers,
Ben
Featured Article(s)
Partitioning Relational Data – (Part 1)
SQL Server 2005 or later versions provides a rich variety of partitioning strategies such as the ability to create partitions for large tables across multiple filegroups. In this article, you will learn why you might want to partition your relational data. You will also learn how to partition tables and how to partition indexes to match partitioned tables.
Featured White Paper(s)
Go Beyond SCOM Monitoring with Foglight for SQL Server
read more)
Featured Script
dba3_move_SqlServer2K_TempDB_demo
move tempdb for SS2K demo… (read more)