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.