SQL Server: Search a Text in Stored Procedure

Sometimes we need to find some objects used in stored procedure or similar other requirement we need to search some text used in Stored procedure. The following lines of code is very useful in for it.

SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%YOURTEXT%'

Replace YOURTEXT with your actual text to be searched.

Statements to Display Sql Server Objects

Followings are the statements to display various objects of SQL Server.

--Data Models

--SQL SERVER 2000

SELECT * FROM dbo.dtproperties
GO

-- SQL SERVER 2005
SELECT * FROM dbo.sysdiagrams;
GO

--Tables

-- SQL SERVER 2000

SELECT * FROM dbo.sysobjects WHERE xtype = 'u' ORDER BY Name
GO

-- SQL SERVER 2005

SELECT * FROM sys.tables ORDER BY Name
GO

--Columns

-- SQL SERVER 2000

SELECT o.name, c.name FROM dbo.syscolumns c INNER JOIN dbo.sysobjects o ON c.id = o.id WHERE o.name = 'MyTableName' ORDER BY c.colorder
GO

-- SQL SERVER 2005

SELECT OBJECT_NAME([Object_ID]) AS 'TableName', [Name] AS 'ColumnName', Column_ID FROM sys.all_columns ORDER BY TableName, Column_ID
GO

-- Primary Keys

-- SQL SERVER 2000

SELECT p.name, OBJECT_NAME(parent_obj) AS 'Table Name' FROM dbo.sysobjects p WHERE p.xtype = 'PK' ORDER BY p.Name
GO

-- SQL SERVER 2005

SELECT OBJECT_NAME(o.parent_object_id) AS 'ParentObject', s.name AS 'Schema', o.Name AS 'PrimaryKey' FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.Type = 'PK' ORDER BY o.Name
GO

-- Foreign Keys

-- SQL SERVER 2000

SELECT OBJECT_NAME(f.constid) AS 'ForeignKey', OBJECT_NAME(f.fkeyid) AS 'FKTable', c1.[name] AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable', c2.[name] AS 'PKColumnName' FROM sysforeignkeys f INNER JOIN syscolumns c1 ON f.fkeyid = c1.[id] AND f.fkey = c1.colid INNER JOIN syscolumns c2 ON f.rkeyid = c2.[id] AND f.rkey = c2.colid ORDER BY OBJECT_NAME(f.rkeyid)
GO

-- SQL SERVER 2005

SELECT OBJECT_NAME(f.constraint_object_id) AS 'ForeignKey', OBJECT_NAME(f.parent_object_id) AS 'FKTable', c1.[name] AS 'FKColumnName', OBJECT_NAME(f.referenced_object_id) AS 'PKTable', c2.[name] AS 'PKColumnName' FROM sys.foreign_key_columns f INNER JOIN sys.all_columns c1 ON f.parent_object_id = c1.[object_id] AND f.parent_column_id = c1.column_id INNER JOIN sys.all_columns c2 ON f.referenced_object_id = c2.[object_id] AND f.referenced_column_id = c2.column_id ORDER BY OBJECT_NAME(f.referenced_object_id)
GO

-- Constraints

-- SQL SERVER 2000

SELECT o.[name] AS 'DefaultName', OBJECT_NAME(c.[id]) AS 'TableName', col.[name] AS 'ColumnName' FROM dbo.sysconstraints c INNER JOIN dbo.sysobjects o ON c.constid = o.[id] INNER JOIN dbo.syscolumns col ON col.[id] = c.colid ORDER BY o.[name]
GO

-- SQL SERVER 2005

SELECT OBJECT_NAME(o.parent_object_id) AS 'ParentObject', s.name AS 'Schema', o.Name AS 'PrimaryKey' FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.Type IN ('C', 'D', 'UQ') ORDER BY o.Name
GO

-- FileGroups\Partitions

-- SQL SERVER 2000

SELECT * FROM sysfilegroups
GO

-- SQL SERVER 2005

SELECT * FROM sys.data_spaces;
GO

-- Stored Procedures

-- SQL SERVER 2000

SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype = 'p' ORDER BY o.[Name]
GO

-- SQL SERVER 2005

SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] = 'p' ORDER BY o.[Name]
GO

-- Functions

-- SQL SERVER 2000

SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype IN ('fn', 'if', 'tf') ORDER BY o.[Name]
GO

-- SQL SERVER 2005

SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] IN ('fn', 'fs', 'ft', 'if', 'tf')ORDER BY o.[Name]
GO

-- Views

--SQL SERVER 2000

SELECT o.[name], o.[id], o.xtype, c.[text] FROM dbo.sysobjects o INNER JOIN dbo.syscomments c ON o.[id] = c.[id] WHERE o.xtype = 'v' ORDER BY o.[Name]
GO

--SQL SERVER 2005

SELECT o.[Name], o.[object_id], o.[type], m.definition FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.[type] = 'V' ORDER BY o.[Name]
GO

Use of CASE statement in MS SQL Server

CASE statement is very use full in sql server if you need to show different value for different conditions. One can use it in SELECT list, WHERE clause, HAVING clause, IN list or in DELETE and UPDATE statements.

When we were upgrading one of our application we need to drop lots of old table and created new tables, to keep running the application, we created equivallent views for the dropped tables and CASE Statement proved very handy to get equivallent columns value.

The general syntax for CASE statement is:

CASE expr
WHEN epr1 THEN returnexp1
[[WHEN expr2 THEN returnexpr2] [...]]
[ELSE exprn]
END


example 1:

CASE @dayId
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wenesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
ELSE 'Wrong Day No'
END

example 2:

DECLARE @var INT
SET @var = 11
SELECT
CASE
WHEN (@var % 2 ) = 0 THEN 'Var is Even'
ELSE 'var is Odd'
END

Tanzil : Quran Navigator

Assalam Allikum!

If u want to learn Quran this site is good for that purpose.

Tanzil : Quran Navigator

Allah Hafiz