T1) Searching for a SQL Object or references of an Object in Stored Procedures
So, while I am preparing for my certification, I thought it would be nice if I also share my other day-to-day SQL challenges/scripts/tricks etc. (Hence the title is numbered with prefix of “T” for Tricks)
I have recently moved to a full on SQL Job from being a .Net Developer. It was a huge change but as I was always into SQL, I decided to take the risk. In my new organization, spending money for IT isn't the most favourite thing. (I am on a Windows XP machine with 3GB RAM and we use SQL Server 2005 for our SQL warehouse, do I need to explain it any further?) So expecting Red Gate Tools is asking for too much. In addition to that, I am working more closely to the finance department and analysts so IT guys treat me like finance boy so I don't really have much control of my own machine that means I can not install Free software similar to Red Gate. Even if I could, I am not sure if my machine would be able to coup up or not.
So I was in the new job, with little support from my system against a huge data warehouse and completely new industry. I have been here for 6 months and I am still figuring out some stuff. In early days, most difficult thing I found was looking for stuff, tables, columns, stored procedure. Someone would just come up and mention a name of process or stored procedure and I will have to figure out where it is and how it works. I had support from my co-workers but I didn't seem right to keep hustling them all the time so I came up with the following query and I turned it into a stored procedure. I always felt that I don't know enough about INFORMATION_SCHEMA so I was so proud of this script when I was done. This is what the script does;
Searches for Column / Table / View / Function / Stored Procedure containing given Keyword
Searches inside Function and Stored Procedure definition for given keyword.
DECLARE @Keyword VARCHAR(128) ,@SQL VARCHAR(MAX) SET @Keyword = 'Address' IF OBJECT_ID('tempdb..#SearchResult') IS NOT NULL BEGIN DROP TABLE #SearchResult END IF OBJECT_ID('tempdb..#SQL') IS NOT NULL BEGIN DROP TABLE #SQL END CREATE TABLE #SearchResult ( RowId INT IDENTITY(1, 1) PRIMARY KEY , DatabaseName VARCHAR (1024) , ObjectName VARCHAR (1024) ,ObjectType VARCHAR(1024) ) CREATE TABLE #SQL ( RowId INT IDENTITY(1, 1) PRIMARY KEY ,Query VARCHAR(MAX) ) INSERT INTO #SQL (Query) SELECT ' select ''' + [Name] + ''' as [DatabaseName], ' + '[name] COLLATE DATABASE_DEFAULT as [ObjectName], [type_desc] COLLATE DATABASE_DEFAULT as [ObjectType] from ' + [name] + '.sys.objects where [name] like ''%' + @Keyword + '%'' ' FROM sys.databases WHERE HAS_DBACCESS(NAME) = 1 AND database_id > 4 UNION ALL SELECT ' SELECT ''' + [Name] + ''' as [DatabaseName], ' + 'o.[name] COLLATE DATABASE_DEFAULT as [ObjectName], o.[type_desc] COLLATE DATABASE_DEFAULT as [ObjectType] FROM ' + [name] + '.sys.objects O INNER JOIN ' + [name] + '.sys.sql_modules m ON m.object_id = o.object_id where m.[definition] like ''%' + @Keyword + '%'' ' FROM sys.databases WHERE HAS_DBACCESS(NAME) = 1 AND database_id > 4 UNION ALL SELECT ' select ''' + [Name] + ''' as [DatabaseName], ' + ' ''Access Denied'' COLLATE DATABASE_DEFAULT, ''Access Denied'' COLLATE DATABASE_DEFAULT from ' + [name] + '.sys.objects where [name] like ''%' + @Keyword + '%'' ' FROM sys.databases WHERE HAS_DBACCESS(NAME) = 0 AND database_id > 4 DECLARE Looper CURSOR FORWARD_ONLY STATIC FOR SELECT Query FROM #SQL ORDER BY RowId OPEN Looper FETCH NEXT FROM Looper INTO @SQL WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #SearchResult ( DatabaseName , ObjectName ,ObjectType ) EXEC (@SQL) FETCH NEXT FROM Looper INTO @SQL END DEALLOCATE Looper SELECT * FROM #SearchResult IF OBJECT_ID('tempdb..#SearchResult') IS NOT NULL BEGIN DROP TABLE #SearchResult END IF OBJECT_ID('tempdb..#SQL') IS NOT NULL BEGIN DROP TABLE #SQL END
Later, I realised that this can be used to find references inside stored procedure across the databases as view dependencies doesn't show you things outside the database object belongs.
Hope it helps
Regards,
Maulin Thaker.