Published on

September 12, 2007

Exploring SQL Server System Tables

SQL Server system tables are internal tables that store information used by SQL Server. While some system tables, like sysobjects and syscolumns, are well-known, there are many others that can be utilized to achieve specific purposes. In this article, we will explore some techniques for working with system tables and how they can be leveraged to enhance your SQL Server experience.

1. Listing Tables and Columns by Column Name

One useful application of system tables is to list all tables where a column name matches a specified value. For example, you can easily find all tables where a column name contains the word “order” by executing the following query:

SELECT o.name as [Table], c.name as [Column]
FROM SYSOBJECTS o
JOIN SYSCOLUMNS c ON o.id = c.id
WHERE c.name LIKE '%order%' AND o.xtype = 'u'
ORDER BY 1

2. Getting Row Count from All Tables

If you need to see the largest tables sorted by their row counts, you can use the following script:

SELECT o.name, i.rowcnt
FROM sysindexes i
JOIN sysobjects o ON i.id = o.id
WHERE indid < 2 AND (OBJECTPROPERTY(object_id(o.name), N'IsTable')) = 1
ORDER BY 2 DESC

3. Finding a String within Any Object

System tables can also be used to find objects, such as stored procedures, views, and functions, where a specified text exists. This can be helpful when you need to change a column name within a table and want to identify all objects that use this column. The following query accomplishes this:

DECLARE @TextPart AS VARCHAR(255)
SET @TextPart = 'search criteria'

SELECT @TextPart = '%' + @TextPart + '%'

SELECT DISTINCT Name,
    CASE
        WHEN OBJECTPROPERTY(sysobjects.id, 'IsProcedure') = 1 THEN 'Stored Procedure'
        WHEN OBJECTPROPERTY(sysobjects.id, 'IsView') = 1 THEN 'View'
        WHEN OBJECTPROPERTY(sysobjects.id, 'IsInlineFunction') = 1 THEN 'Inline Function'
        WHEN OBJECTPROPERTY(sysobjects.id, 'IsScalarFunction') = 1 THEN 'Scalar Function'
        WHEN OBJECTPROPERTY(sysobjects.id, 'IsTableFunction') = 1 THEN 'Table Function'
        WHEN OBJECTPROPERTY(sysobjects.id, 'IsTrigger') = 1 THEN 'Trigger'
    END AS ObjectType
FROM syscomments
JOIN sysobjects ON sysobjects.id = syscomments.id
WHERE PATINDEX(@TextPart, text) > 0
ORDER BY Name

4. Adding a Default Value to a Column

If you need to generate an ALTER TABLE script to add a DEFAULT value to a specific column, you can use the following query:

SELECT 'ALTER TABLE ' + name + ' ADD DEFAULT getdate() FOR LastModifiedDate WITH VALUES'
FROM sysobjects
WHERE id IN (
    SELECT id
    FROM syscolumns
    WHERE name = 'LastModifiedDate' AND cdefault = 0 AND isnullable = 0
) AND type = 'u'
ORDER BY 1

5. Listing all Parameters from a Stored Procedure

To create a list of parameters for calling a stored procedure from another environment, you can use the following query:

-- c.isoutparam = 1 for output parameters
-- c.isoutparam = 0 for input parameters
SELECT ParamName = c.name,
    DataType = t.name,
    c.Length,
    Direction = CASE c.isoutparam
        WHEN 0 THEN 'adParamInput'
        WHEN 1 THEN 'adParamInputOutput'
    END,
    c.colorder
FROM syscolumns c,
    sysobjects o,
    systypes t
WHERE c.[id] = o.id
    AND t.xusertype = c.xusertype
    AND o.[name] = 'Sp name'
ORDER BY c.colorder

6. Finding a String within Any Table

If you need to find out how an application spreads values within a database, you can use the following query:

SET NOCOUNT ON

DECLARE @value VARCHAR(1000)
SET @value = 'Put searching criteria here'

SELECT 'IF EXISTS(SELECT [' + c.name + '] FROM [' + o.name + '] WHERE [' + c.name + '] LIKE ''%' + @value + '%'')'
    + CHAR(13) + 'SELECT [' + c.name + '], ''Tbl'' AS [' + o.name + '] FROM [' + o.name + '] WHERE [' + c.name + '] LIKE ''%' + @value + '%''' + CHAR(13) + CHAR(13)
FROM syscolumns c
INNER JOIN systypes t ON t.xtype = c.xtype
INNER JOIN sysobjects o ON o.id = c.id
WHERE o.type = 'u'
    AND c.id > 500
    AND t.name IN ('varchar', 'char')
ORDER BY 1

7. Backing up Table Values

To synchronize or transfer table values, you can use the following query:

DECLARE @Script VARCHAR(8000),
    @column_list VARCHAR(1000),
    @decl VARCHAR(50),
    @Table_Name VARCHAR(50),
    @q CHAR(1)

-- Column Info variables
DECLARE @column_name VARCHAR(65),
    @column_data_type VARCHAR(30),
    @column_id INT,
    @ident_flag BIT

SET NOCOUNT ON

SET @Script = ''
SET @column_list = ''
SET @q = CHAR(39)
SET @table_name = 'tablename' -- change table name here

-- Cursor for the columns within a table
DECLARE c_columns CURSOR FOR
SELECT c.name, t.name, c.colid
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
JOIN systypes t ON c.xusertype = t.xusertype
WHERE o.NAME = @table_name
    AND t.name NOT IN ('timestamp')
ORDER BY c.colid

OPEN c_columns

FETCH NEXT FROM c_columns INTO @column_name, @column_data_type, @column_id

-- Check if table has IDENTITY property set to YES
SELECT @ident_flag = COUNT(1)
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
WHERE o.NAME = @table_name
    AND COLUMNPROPERTY(c.id, c.name, 'IsIdentity') = 1

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Script = @Script + (
            SELECT CASE
                    WHEN @column_data_type IN ('varbinary', 'binary', 'varchar', 'char', 'nchar', 'nvarchar', 'datetime', 'smalldatetime') THEN 'CASE WHEN ' + @column_name + ' IS NOT NULL THEN @q + LTRIM(RTRIM(' + @column_name + ')) + @q ELSE ''NULL'' END + '','' + '
                    ELSE /*'cast(' + @column_name + ' as varchar(50))  + '','' + ' END) + CHAR(13)*/ 'CASE WHEN ' + @column_name + ' IS NOT NULL THEN  + CAST(' + @column_name + '  AS varchar(50)) ELSE ''NULL'' END + '','' + '
                END) + CHAR(13)

    -- IF @ident_flag = 1
    SET @column_list = @column_list + @column_name + ','
    FETCH NEXT FROM c_columns INTO @column_name, @column_data_type, @column_id
END

DEALLOCATE c_columns

SET NOCOUNT OFF

-- IF @ident_flag = 1
SET @column_list = '(' + LEFT(@column_list, LEN(@column_list) - 1) + ')'

SET @Script = LEFT(@Script, LEN(@Script) - 6) + ')'
SET @Script = 'SELECT ' + @q + 'INSERT ' + @Table_name + @column_list + @q + '+ CHAR(13) +' + @q + 'VALUES (' + @q + '+' + CHAR(13) + @Script + @q + CHAR(13) + 'FROM ' + @Table_name

SET @decl = 'DECLARE @q CHAR(1) SET @q = CHAR(39) '

-- IF @ident_flag = 1
PRINT 'SET IDENTITY_INSERT ' + @Table_name + ' ON'

-- PRINT @decl + @Script
-- EXECUTE (@decl + @Script)

SET @Script = ''

-- IF @ident_flag = 1
PRINT 'SET IDENTITY_INSERT ' + @Table_name + ' OFF'

8. Automating Insert Stored Procedure

To create an INSERT or UPDATE stored procedure, you can use the following query:

DECLARE @ParamDeclaration VARCHAR(5000),
    @InsertColNames VARCHAR(5000),
    @InsertValues VARCHAR(5000),
    @Script VARCHAR(8000),
    @ThrowError VARCHAR(500),
    @Param VARCHAR(100),
    @DropSprocScript VARCHAR(8000),
    @Table_Name VARCHAR(50),
    @Prefix VARCHAR(15),
    @obj_name VARCHAR(65),
    @ExceptionMessage VARCHAR(200),
    @CRLF VARCHAR(9)

-- Column Info variables
DECLARE @column_name VARCHAR(65),
    @column_type VARCHAR(30),
    @column_length INT,
    @column_id INT

SET NOCOUNT ON

SELECT @ParamDeclaration = '',
    @InsertColNames = '',
    @InsertValues = '',
    @Script = '',
    @ThrowError = '',
    @CRLF = CHAR(10) + CHAR(13)

SET @Prefix = 'My_Prefix_' -- change Stored Procedure prefix here
SET @table_name = 'Customers' -- change table name here
SET @ExceptionMessage = CHAR(39) + 'An error occurred when trying to INSERT. Table = ' + @table_name + '. SProc = ' + @Prefix + @table_name + CHAR(39)
SET @obj_name = @Prefix + @table_name

SET @DropSprocScript = 'IF EXISTS (SELECT * FROM sysobjects WHERE [id] = object_id(N' + CHAR(39) + '[dbo].[' + @obj_name + ']' + CHAR(39) + ') and OBJECTPROPERTY(id, N' + CHAR(39) + 'IsProcedure' + CHAR(39) + ') = 1)' + ' DROP PROCEDURE [dbo].[' + @obj_name + ']' + CHAR(13) + 'GO'
SET @DropSprocScript = @DropSprocScript + CHAR(13)

SET @Script = @Script + CHAR(13) + 'CREATE PROCEDURE ' + @obj_name

-- Cursor for the columns within a table
DECLARE col_details CURSOR FOR
SELECT c.name, t.name, c.length, c.colid
FROM sysobjects o
JOIN syscolumns c ON c.id = o.id
JOIN systypes t ON c.xusertype = t.xusertype
WHERE o.NAME = @table_name
    AND COLUMNPROPERTY(c.id, c.name, 'IsIdentity') = 0
ORDER BY c.colid

OPEN col_details

FETCH NEXT FROM col_details INTO @column_name, @column_type, @column_length, @column_id

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Param = '@' + @column_name + ' ' + @column_type + (SELECT CASE WHEN @column_type IN ('varbinary', 'binary', 'varchar', 'char', 'nchar', 'nvarchar') THEN '(' + CAST(@column_length AS VARCHAR(10)) + ')' ELSE '' END)
    SET @InsertColNames = @InsertColNames + @column_name + ', '
    SET @InsertValues = @InsertValues + '@' + @column_name + ', '

    SELECT @ParamDeclaration = @ParamDeclaration + CHAR(9) + @Param + ',' + CHAR(13)

    FETCH NEXT FROM col_details INTO @column_name, @column_type, @column_length, @column_id
END

DEALLOCATE col_details

SET NOCOUNT OFF

SET @ParamDeclaration = @ParamDeclaration + CHAR(9) + '@RowAffected INT = 0 OUTPUT,' + CHAR(13) + CHAR(9) + '@ValidCode INT = 0 OUTPUT,' + CHAR(13) + CHAR(9) + '@AffectedID INT = 0 OUTPUT'

SET @InsertColNames = LEFT(@InsertColNames, LEN(@InsertColNames) - 1)
SET @InsertValues = LEFT(@InsertValues, LEN(@InsertValues) - 1)

SET @Script = @Script + CHAR(13) + @ParamDeclaration + CHAR(13) + 'AS' + @CRLF
SET @Script = @Script + 'BEGIN TRANSACTION' + @CRLF
SET @Script = @Script + 'INSERT ' + @Table_name + CHAR(13) + CHAR(9) + '(' + @InsertColNames + ')' + CHAR(13)
SET @Script = @Script + 'VALUES' + CHAR(13)
SET @Script = @Script + CHAR(9) + '(' + @InsertValues + ')' + @CRLF

-- Add Error Handling and Goto tags
SET @ThrowError = CHAR(9) + 'SET @RowAffected = @@RowCount' + @CRLF
SET @ThrowError = @ThrowError + CHAR(9) + 'IF @@Error !=0 GOTO ErrorCondition' + @CRLF
SET @ThrowError = @ThrowError + CHAR(13) + 'GOTO Success_COMMIT' + @CRLF
SET @ThrowError = @ThrowError + 'ErrorCondition:' + CHAR(13)
SET @ThrowError = @ThrowError + CHAR(9) + 'ROLLBACK TRANSACTION' + CHAR(13)
SET @ThrowError = @ThrowError + CHAR(9) + 'RAISERROR(' + @ExceptionMessage + ', 16, 1)' + CHAR(13)
SET @ThrowError = @ThrowError + CHAR(9) + 'GOTO SProcReturn' + CHAR(13)
SET @ThrowError = @ThrowError + 'Success_COMMIT:' + CHAR(13)
SET @ThrowError = @ThrowError + CHAR(9) + 'COMMIT TRANSACTION' + CHAR(13)
SET @ThrowError = @ThrowError + 'SProcReturn:' + CHAR(13)
SET @ThrowError = @ThrowError + CHAR(9) + 'SET @ValidCode = @@Error' + CHAR(13)
SET @ThrowError = @ThrowError + CHAR(9) + 'SET @AffectedID = @@Identity' + @CRLF
SET @ThrowError = @ThrowError + CHAR(9) + 'RETURN @ValidCode' + @CRLF

SET @Script = @Script + @ThrowError

SET @InsertValues = ''
SET @InsertColNames = ''
SET @ParamDeclaration = ''
SET @Script = @Script + @CRLF + @CRLF

PRINT @DropSprocScript
PRINT @Script

-- EXECUTE (@DropSprocScript)
-- EXECUTE (@Script)

SET @DropSprocScript = ''
SET @Script = ''

Note: These examples provide basic principles and may require customization to fit your specific needs.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.