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.