Looks like it's about time for another post!
This one is about the SQL Server system tables. These are a fav of mine because I find them so useful in scripts.
The caveats when dealing with them are you need to be mindful of SQL Server versions. Everything that worked on SQL Server 7.0 will work on SQL Server 2000, but there are some minor tweaks in SQL Server 2000 that are not valid in SQL Server 7.0. Now is not a good time to mention SQL Server <= v6.5 because the system tables had an overhaul for 7.0, and I haven't checked any of this code on Yukkon/SQL Server 2005 yet.
My fav thing to use the tables for is dealing with object existance in scripts. In my books a good T-SQL script can be run over and over without damaging the database. Put another way, if your SQL Script throws an error if it is run twice against the same database it's not a good T-SQL script.
Consider we are dealing with the following table:
CREATE TABLE testing_data (
pkey INT IDENTITY (1,1) NOT NULL,
created DATETIME DEFAULT getdate() NOT NULL,
modified DATETIME NULL,
deleted BIT DEFAULT 0 NOT NULL,
testing_val_1 NVARCHAR(15) NOT NULL,
testing_val_2 NVARCHAR(255)
)For whatever reason we want to drop and recreate this table each run, you could put the following statement before it:
IF Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testing_data]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE testing_data
GO
This is the syntax you will see if you choose to include Drops in scripts you generate from the Enterprise Manager, but I don't use it much, mainly because I can never remember the OBJECTPROPERTY() syntax!
Typically I do the following:
IF Exists(SELECT [id] FROM sysobjects
WHERE sysobjects.[name] = N'testing_data'
AND sysobjects.[type] = N'U')
DROP TABLE testing_data
GO
I feel that Microsoft are hinting us towards using OBJECTPROPERTY() for future version compatability, but I still favor this syntax because apart from being easy to remember it's easy to adapt for other kinds of objects, e.g:
IF Exists(SELECT [id] FROM sysobjects
WHERE sysobjects.[name] = 'prGetTestingDataRows'
AND sysobjects.[type] = 'P')
DROP PROC prGetTestingDataRows
GO
So if the Type column in sysobjects is 'U' for user tables, and 'P' for procedures then there are no prizes for guessing what TR, D & V might mean.
For a more complex example, lets say you want to change the type of the fileds testing_val_1 to NVARCHAR(35) ONLY if it has not been changed before, you could wrap the ALTER TABLE stateement in the following BEGIN... END:
IF Exists(SELECT syscolumns.[name]
FROM syscolumns
LEFT JOIN sysobjects
ON syscolumns.[id] = sysobjects.[id]
LEFT JOIN systypes
ON syscolumns.[xtype] = systypes.[xtype]
WHERE syscolumns.[name] = 'testing_val_1'
AND systypes.[name] = 'nvarchar'
AND sysobjects.[name] = 'testing_data')
BEGIN
ALTER TABLE -- ... Implementation ommited for clarity
END
So having only touched two or three sys tables we have a couple of good tools that are easy to use. I'll cover more at a later date, in the mean time enjoy the extra metadata!
After blog mint [?]:
Here's an actual practical example from a script I have been working on recently (user name changed). This script makes a dozen or so procs, the svcapp account is a login used by a service that only has rights to exec these procs, and no rights granted to the base tables. This automates granting access to the created procs, and it much quicker than doing it in the SQL EM:
PRINT 'PART 4 - Granting access to user account'
GO
DECLARE @sql NVARCHAR(512)
DECLARE @name NVARCHAR(128)
DECLARE @usernm NVARCHAR(128)
DECLARE cr CURSOR FOR
SELECT [name] FROM sysobjects
WHERE type='P'
ORDER BY [name]
SET @usernm = 'svcapp'
OPEN cr
FETCH NEXT FROM cr INTO @name
WHILE @@fetch_status = 0
BEGIN
SET @sql = 'grant exec on ' + @name + ' to ' + @usernm
EXEC sp_executesql @sql
PRINT 'Granting EXEC on ' + @name + ' to user: '
FETCH NEXT FROM cr INTO @name
END
CLOSE cr
DEALLOCATE cr
PRINT 'Done.'
GO