SQL System Views

Using SQL 2005 System Views

SQL has numerous system views containing META data about the system. In fact I would say in SQL 2005 there are probably too many system views as they have kept views from previous versions to be backwards compatible and therefore you are spoilt for choice when it comes to accessing system data. For example the following 4 SELECT statements are all examples of accessing meta data about the tables you have created within a database. Notice how each SELECT uses a different system view.

SELECT TOP 1*
FROM INFORMATION_SCHEMA.TABLES
ORDER BY  TABLE_NAME

SELECT TOP 1 *
FROM sys.tables
WHERE type='U'
ORDER BY Name

SELECT TOP 1 *
FROM sys.sysobjects
WHERE xtype='U'
ORDER BY Name

SELECT TOP 1 *
FROM sys.objects
WHERE type = 'U'
ORDER BY Name

Each statement should return the same table name with varying amounts of related data.

Using System Views to create SQL

There are so many different ways that a working knowledge of the system views can be beneficial but one of the ways is to help you write SQL statements which would otherwise take a long time by hand. For example today at work I had to change the value of a certain column from A to B. The column was a key used for identifying websites. It resided in a system that used 4 databases and appeared in nearly 80% of all the tables, so writing the statements by hand would have taken a long time. The idea was to sync up our development and production server so that this column value was the same for both. Therefore I knew the names of the databases that needed updating as well as the name of the column, the existing value and the new value. I needed to output all the relevant UPDATE statements (177 in total) to do the required changes.

Filtering databases using sys.databases

After setting up some variables I created a local temp table which I populated with the names of the databases that I needed to create UPDATE statements for. I used the system view sys.databases for this e.g

CREATE TABLE #DB(DB INT,DBNAME NVARCHAR(200))

--set DBs we want to check for
INSERT INTO #DB
SELECT database_id,Name
FROM master.sys.databases
WHERE Name LIKE 'mydatabase_system_%'

Then I used the undocumented system stored procedure sp_MSForEachDB to loop through all the databases on the server checking each one to see if it also matches a database in my temp table #DB and if so I run a SELECT statement that takes data from 4 more system views to populate another temporary table #TABLES. This temp table will hold all the information I need to create the necessary SQL including the database name, table name and schema owner.

EXEC sp_MSForEachDB 'USE [?];
IF DB_ID() IN(SELECT DB FROM #DB)
BEGIN
INSERT INTO #TABLES
SELECT d.Name,u.Name, o.Name, c.Name
FROM sys.objects as o
JOIN sys.columns as c
ON o.object_id = c.object_id
JOIN sysusers as u
ON u.uid = o.schema_id
JOIN sys.databases as d
ON d.database_id = db_id()
WHERE c.Name = ''SiteFk''
AND o.type = ''U''
AND o.name LIKE ''tbl_%''
AND c.is_identity=0
ORDER BY o.Name
END'

Notice that I am using the latest system views that come with SQL 2005 and I am returning data that will enable me to create the necessary UPDATE statements with fully qualified names.

Once I have populated my working tables I can create the UPDATE statements. As the column I am updating is used in numerous foreign key constraints I need to disable these constraints before carrying out the update and then re-enable them once the update has completed. Therefore for each table in my loop I output 3 SQL statements.

-- Create ALTER statement to disable foreign key constraints
SELECT @SQL = 'ALTER TABLE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' NOCHECK CONSTRAINT ALL'+@NL+@NL
FROM #TABLES
WHERE RowNo = @Row

-- Create UPDATE sql to change old value to new value
SELECT @SQL = @SQL + 'UPDATE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' '+@NL + 'SET ' + DBCol + ' = ' + CAST(@NewVal as varchar(5)) + @NL + 'WHERE '+DBCol + ' = ' + CAST(@OldVal as varchar(5)) + ';' + @NL+@NL
FROM #TABLES
WHERE RowNo = @Row

-- Create ALTER statement to enable foreign key constraints
SELECT @SQL = @SQL + 'ALTER TABLE '+QUOTENAME(DB)+'.'+QUOTENAME(DBUser)+'.'+QUOTENAME(DBTable)+' CHECK CONSTRAINT ALL'+@NL+@NL
FROM #TABLES
WHERE RowNo = @Row

Each loop iteration will PRINT out the value for the @SQL variable and after running the code within a second I have 531 SQL statements to run including 177 UPDATE statements e.g:

ALTER TABLE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS] NOCHECK CONSTRAINT ALL

UPDATE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS]
SET SiteFK = 5000
WHERE SiteFK = 116;

ALTER TABLE [mydatabase_system_examplea].[dbo].[tbl_BANNER_LOCATIONS] CHECK CONSTRAINT ALL

So even with the time it took me to write this little script it still outweighed the amount of time it would have taken me to do the work manually e.g find all tables across 4 databases that included the column I needed to update and then write the necessary SQL. Plus if I need to do something similar in the future I can just tweak the script.

To view the full SQL script that I used click here