Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Working With System Tables

By Alex Grinberg,

All we know SQL Server has system tables, which store information internally used by SQL Server – someone knows all of them
someone few like sysobjects and syscolumns.
In this article I would like to share some techniques that I have used to work with system tables to
attain certain purposes, and how to make them work for you.

We can use system tables not only to monitor SQL Server’s behavior, search for objects, check run-time status, and so on.
We can use T-SQL to generate practical re-usable code to augment business logic.
I have used several scripts to generate code for VB, ASP, and C#. Actually, there is no programming language limit in this case.
It all depends on what you expect to get from a script. So let’s take a look at several niftiest examples.

I am pretty sure that you may have resorted quire a few times to producing your own code generators, one way or another.
For some developers and DBAs, this code may give an idea about how to use it to generate other similar code.

/*************** 1. List tables, columns by Column(table) name 
Useful to list all tables where column name matched with value specified in WHERE CLAUSE. 
For example, list all tables where a column name contains “order” as (a part of) its name.
*****************/

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. Get row count from all Tables 
If you need to see the largest tables by sorted by their rowcounts, the following script 
returns such list.
 ****************************/

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. Find string within any object 
Find objects like stored procedure, view, function where specified text exists. 
For example: for some reason you need to change column name within a table and number of 
stored procedure, view and function already using this column name, so you need to find out 
all objects that use this column.
*****************************/

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. ADD default to column 
This SQL generates ALTER TABLE script if for whatever purpose you may need. For example, to 
add a DEFAULT for a specific column. If a table already has DEFAULT for the specified column, 
then this table does not appears in the result set.
You need to run the SQL first, then copy result to the new window and run all generated scripts 
to apply changes.
*********************************/
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. How to List all Parameters from SP 
Very useful to create something like an ADO parameters list to call a stored procedure from 
another environment. Especially when a stored procedure has more then 5 parameters. This is 
basically my time saver.  This SQL have very basic script, so you may need modify it according your 
needs.
***********************/

-- c.isoutparam = 1 this is output parameters
-- c.isoutparam = 0 this is 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. Find string within any table In case when you need to find out how application spread the values within database, this SQL could help you in this case. You need to run the SQL first, then copy result to the new window and run all generated script to see a result. *****************************/ 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. Back up any table values This SQL works well to synchronize or transfer a table values. I have had a number of situations when DTS was not a good choice for this task. Some of you use database comparison tools like ‘Red Gate’, in case you do not have these tools – use SQL below. You need to run the SQL first, then copy result to the new window, delete or truncate data from a table and run all generated script to apply changes. ********************************/ 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. Automate Insert Stored Procedure Very efficient to create INSERT or UPDATE stored procedure. You need to run the SQL first, then copy result to the new window and run all generated script to apply changes. *************************/ 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: All examples utilize basic principles, and may require some customization to fit your particular needs.

Total article views: 14399 | Views in the last 30 days: 12
 
Related Articles
FORUM

Updating varchar(max) column

Updating varchar(max) column

FORUM

Update Script to select

Update Script to select

FORUM

Varchar column conversion

Converting a column with Data Type of Varchar to Datetime

SCRIPT

Aggregrating varchar columns

Concatenate columns grouping on Index column (approach for summing varchar columns by grouping on in...

SCRIPT

Make string columns unicode compliant

Generates scripts to make text, ntext, varchar and char columns nvarchar/nchar

Tags
sql puzzles    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones