Using SQL to Retrieve SQL
At work, someone made a request that required me to look through potentially hundreds of views in dozens of databases on our SQL Server. I certainly didn't want to examine each one at a time. How could I speed up this process with code?
Well, you can find all of your views by querying the sysobjects table, and you can retrieve the SQL behind the views by querying the syscomments table. Something like this works well:
RTrim(syscomments.text) AS ViewSQL
FROM sysobjects JOIN syscomments
ON syscomments.id=sysobjects.id
WHERE sysobjects.xtype='V' AND sysobjects.category=0
This will retrieve the SQL code and names of all the views in the current database. This simple query is the heart of the solution. But I would like to retrieve this information for all of the databases.
Well, it's easy enough to get a list of all the databases. The sysdatabases table in the master database has that list. You can query that table, perhaps filtering out some of the system or sample databases included with SQL Server:
WHERE name NOT IN ('tempdb','master','msdb','pubs','model')
ORDER BY name
Now just combine this information. To accomplish this, we'll build a stored procedure that will create a temporary table, loop through the databases and query each one for its views, insert the view information into the temporary table, and return the temporary table.
Something like this will do the trick:
BEGIN
-- Vars
DECLARE @dbname sysname
-- Temp Table
CREATE TABLE #Results
(
DatabaseName varchar(200),
ViewName varchar(200),
ViewText nvarchar(4000)
)
-- Loop Thru the Databases.
DECLARE dbnames_cursor CURSOR
FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb','master','msdb','pubs','model')
ORDER BY name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @dbname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
-- Grab the Views of this Database and Put them in the Temp Table.
SET @dbname = RTRIM(@dbname)
INSERT INTO #Results
EXECUTE
(
'SELECT '''+@dbName+''' as DatabaseName, ' +
'RTrim(' + @dbname + '.dbo.sysobjects.name) as ViewName, ' +
'RTrim(' + @dbname + '.dbo.syscomments.text) as ViewText ' +
'FROM ' + @dbName + '.dbo.sysobjects join ' + @dbName + '.dbo.syscomments ' +
'ON ' + @dbName + '.dbo.syscomments.id=' + @dbName + '.dbo.sysobjects.id ' +
'WHERE ' + @dbname + '.dbo.sysobjects.xtype=''V'' and ' + @dbname + '.dbo.sysobjects.category=0 '
)
END
FETCH NEXT FROM dbnames_cursor INTO @dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
SELECT * FROM #Results order by DatabaseName, ViewName
DROP TABLE #Results
END
--
Now just execute the stored procedure and review its output.
This portion of the solution just retrieves the data. After writing this, I developed a really short and simple ColdFusion application that would output the database name, view name, and SQL to a table, and used some simple JavaScript to make it easier to search and filter the views. The client-side methods used to view and work with the data are obviously up to you.