Thursday, May 15, 2008

Get the names of all tables used in a SQL Server view

I want to be able to quickly identify the tables which make up a SQL Server view. I know I can just open the script and look at it, but I have the following problems:
  1. All the scripts are one liners for some strange reason.
  2. And some of the views contain large numbers of tables in the joins.
So I came up with this script. It will work in SQL Server 2000 and 2005.

Take note: This script is by no means foolproof, and I haven't tested it very extensively. I am even ignoring the tables' schemas altogether. And if a view and a table would have the same name my script wouldn't know the difference.
But hey, it returns the information I want, so I am happy.

I didn't use the system view INFORMATION_SCHEMA.VIEWS because it truncates a view's T-SQL script in the VIEW_DEFINITION column when the script is too long.

I rather used syscomments because it spreads a view's script over several records when the script is too long for the text column. This enabled me to search the whole script for tables names.
I am not sure if syscomments will split a script in the middle of a word when the script is too long for the text column. If that would be the case I could miss some table names because they can be split.
(syscomments is a system view in the sys schema in SQL Server 2005. In SQL Server 7.0 and 2000 it is a system table in the dbo schema.)

No comments: