Working With System Tables

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