Tuesday 5 June 2012

Use Sysobjects in SQL Server to Find Useful Database Information


Everything about your SQL Server database is stored in its system tables. I doubt that you spend a lot of time inspecting the system tables since you are too busy with your user tables. However, you may need to do something unusual once in a while, such as list all the triggers in your database. You could inspect your tables one by one, but that can become quite labor intensive if you have 500 tables.
This is where knowledge of the sysobjects table comes in handy. While it is not recommended that you update this table, you are certainly allowed to interrogate it.
In most cases, the two columns most useful to you will be sysobjects.name and sysobjects.xtype. The former lists the names of the objects in question, while the latter identifies the type of object, using the following codes:
  • C: Check constraint
  • D: Default constraint
  • F: Foreign Key constraint
  • L: Log
  • P: Stored procedure
  • PK: Primary Key constraint
  • RF: Replication Filter stored procedure
  • S: System table
  • TR: Trigger
  • U: User table
  • UQ: Unique constraint
  • V: View
  • X: Extended stored procedure
In the case of triggers, three other columns that identify the type of trigger are of interest: deltrig, instrig, and uptrig.
You can list all the objects of any type of interest using the following:
SELECT * FROM sysobjects WHERE xtype = <type of interest>
In the special case of triggers, which are owned by their parent table, you might want to interrogate the database using a self-join, like this:
SELECT 
      Sys2.[name] TableName, 
      Sys1.[name] TriggerName, 
      CASE 
            WHEN Sys1.deltrig > 0 THEN'Delete' 
            WHEN Sys1.instrig > 0 THEN'Insert' 
            WHEN Sys1.updtrig > 0 THEN'Update' 
      END'TriggerType' 
FROM 
      sysobjects Sys1 JOIN sysobjects Sys2 ON Sys1.parent_obj = Sys2.[id] 
WHERE Sys1.xtype='TR'
ORDERBY TableName
In SQL Server 2005, the preferred technique is to use the system views. This approach will insulate your queries from any changes that Microsoft might choose to make to the system tables.
Here is a simple example, using the INFORMATION_SCHEMA_TABLES view:
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE 
FROMINFORMATION_SCHEMA.TABLES
ORDERBY TABLE_SCHEMA, TABLE_NAME
Run this query against the AdventureWorks database or any of your own databases to produce a quick list of the tables.
In order to illustrate the power of these schema queries, look at the following statement, which will list all functions and procedures within the selected database:
SELECT*FROMINFORMATION_SCHEMA.ROUTINES
ORDERBY ROUTINE_TYPE, ROUTINE_NAME

sys.procedure in sql server


 if you want to search for specific information about stored procedures in your database. Rather than look one by one through the code, you can query the SQL Server sys.objects and sys.procedures tables to find exactly what you are looking for.
To search for stored procedures that were created or modified on a specific date, you can directly search either the sys.objects and sys.procedures tables.


You can run exactly the same query against the sys.procedures table. Since this table is dedicated to storing detailed information about your database’s stored procedures you do not need to include a type filter with your query.
USE DatabaseName
GO 
SELECT modify_date,name,create_date
FROM sys.procedures
ORDER BY modify_date DESC
GO
As you can see, the sys.procedures table is quite useful, but you can use it to query more than just the name and create/modify dates.
For example, you can query the sys.prodedures table for stored procedures that contain specific text. This is really a handy function that can be useful if you are combing through an unfamiliar database or want to find very specific code quickly. You can even return the text of the procedure directly in your query so that you can quickly analyze each of the returned results quickly without having to find and open the procedure in SSMS.
Here is an example query that filters stored procedures to return only ones that contain the SQL CHARINDEX function:
USE DatabaseName
GO
SELECT modify_date,name,create_date
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%charindex%'
ORDER BY modify_date DESC
GO