You’re probably never going to be familiar with every database object, i.e. Table, View, Stored Procedure and Function, that exists in a large production database, even if you were the one who designed it. So everyone who maintains an environment be it a call center back end or sales system back end or CRM back end etc. etc. needs to be able to locate objects quickly.
Redgate offer a fantastic free tool to do this within SQL Server Management Studio through a GUI called SQL Search.
As great as this is though sometimes you might want to search through object definitions programmatically.
To clarify I’m defining object definition as being column names of a table or view or the command that makes up a stored procedure or function.
Below is the Store Procedure I’ve written to do this called SearchObjectDefinition. To work this Stored Procedure also requires the User Defined Function (UDF) called Split which I used in the tutorial “How to pass a multi-value parameter to a stored procedure from a SSRS Report“.
Below are a few use cases for SearchObjectDefinition:
--List All Instance Tables, Stored Procedures, Views and Functions
EXEC dbo.SearchObjectDefinition
--List All Stored Procedures, and Functions in the Databases
--TestDatabaseOne and TestDatabaseTwo
EXEC dbo.SearchObjectDefinition @ObjectType = 'Sp, Fn'
,@DatabaseName = 'TestDatabaseOne, TestDatabaseTwo'
--List All Instance Tables, Stored Procedures, Views and Functions
--where Object Definition contains the word Insert
EXEC dbo.SearchObjectDefinition @strFind = 'insert'
--List All Instance Tables where Object Name is Customers and
--Column name contains the word Phone
EXEC dbo.SearchObjectDefinition @ObjectType = 'tb'
,@ObjectName = 'Customers'
,@strFind = 'Phone'
As always be sure to deploy the following Function and Store Procedure in a utility database not the master database as this is bad practice.
Split Function:
--USE [DatabaseName];
--GO
IF OBJECT_ID('[Split]') IS NULL
EXEC ('CREATE FUNCTION dbo.[Split](@i INT) RETURNS @RtnValue TABLE (j INT) AS BEGIN INSERT INTO @RtnValue (j) SELECT 1 RETURN END');
GO
ALTER FUNCTION [dbo].[Split] (
@List NVARCHAR(2000)
,@SplitOn NVARCHAR(5)
)
RETURNS @RtnValue TABLE (
Id INT identity(1, 1)
,Value NVARCHAR(100)
)
AS
BEGIN
WHILE (Charindex(@SplitOn, @List) > 0)
BEGIN
INSERT INTO @RtnValue (value)
SELECT Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))
SET @List = Substring(@List, Charindex(@SplitOn, @List) + len(@SplitOn), len(@List))
END
INSERT INTO @RtnValue (Value)
SELECT Value = ltrim(rtrim(@List))
RETURN
END
SearchObjectDefinition Stored Procedure:
--USE [DatabaseName];
--GO
IF OBJECT_ID('[SearchObjectDefinition]') IS NULL
EXEC ('CREATE PROCEDURE dbo.[SearchObjectDefinition] AS SELECT 1')
GO
ALTER PROCEDURE [dbo].[SearchObjectDefinition] (
@ObjectType AS VARCHAR(20) = NULL
,@ObjectName AS SYSNAME = NULL
,@DatabaseName AS SYSNAME = NULL
,@strFind AS VARCHAR(MAX) = NULL
)
AS
BEGIN
SET NOCOUNT ON;
SET @strFind = ISNULL(@strFind, '')
SET @ObjectName = ISNULL(@ObjectName, '')
IF OBJECT_ID('tempdb..#Result') IS NOT NULL
DROP TABLE #Result;
DECLARE @DatabaseTable TABLE (DbName SYSNAME)
DECLARE @DbName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)
CREATE TABLE #Result (
DbName SYSNAME NULL
,ObjectType VARCHAR(2)
,ObjectName SYSNAME
,ObjectDefinition VARCHAR(MAX)
)
IF @DatabaseName IS NOT NULL
BEGIN
INSERT INTO @DatabaseTable (DbName)
SELECT Value
FROM dbo.Split(@DatabaseName, ',')
END
IF @DatabaseName IS NULL
BEGIN
INSERT INTO @DatabaseTable (DbName)
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME NOT IN (
'tempdb'
,'master'
,'msdb'
,'model'
)
ORDER BY NAME ASC
END
SET @DbName = ''
--TO FIND STRING IN ALL PROCEDURES
IF @ObjectType LIKE '%Sp%'
OR @ObjectType IS NULL
BEGIN
WHILE @DbName IS NOT NULL
BEGIN
SET @DbName = (
SELECT MIN(DbName)
FROM @DatabaseTable
WHERE DbName > @DbName
)
SET @Sql = '
USE ' + QUOTENAME(@DbName) + ';
INSERT INTO #Result (
DbName
,ObjectType
,ObjectName
,ObjectDefinition
)
SELECT ''' + @DbName + ''' AS DbName
,''Sp'' AS ObjectType
,OBJECT_NAME(OBJECT_ID) AS ObjectName
,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
FROM ' + QUOTENAME(@DbName) + '.sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
'
EXEC (@Sql)
END
END
SET @DbName = ''
--TO FIND STRING IN ALL VIEWS
IF @ObjectType LIKE '%Vw%'
OR @ObjectType IS NULL
BEGIN
WHILE @DbName IS NOT NULL
BEGIN
SET @DbName = (
SELECT MIN(DbName)
FROM @DatabaseTable
WHERE DbName > @DbName
)
SET @Sql = '
USE ' + QUOTENAME(@DbName) + ';
INSERT INTO #Result (
DbName
,ObjectType
,ObjectName
,ObjectDefinition
)
SELECT ''' + @DbName + ''' AS DbName
,''Vw'' AS ObjectType
,OBJECT_NAME(OBJECT_ID) AS ObjectName
,OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
FROM ' + QUOTENAME(@DbName) + '.sys.VIEWS
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'' + ''' + @strFind + ''' + ''%''
AND Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
'
EXEC (@Sql)
END
END
SET @DbName = ''
--TO FIND STRING IN ALL FUNCTION
IF @ObjectType LIKE '%Fn%'
OR @ObjectType IS NULL
BEGIN
WHILE @DbName IS NOT NULL
BEGIN
SET @DbName = (
SELECT MIN(DbName)
FROM @DatabaseTable
WHERE DbName > @DbName
)
SET @Sql = '
USE ' + QUOTENAME(@DbName) + ';
INSERT INTO #Result (
DbName
,ObjectType
,ObjectName
,ObjectDefinition
)
SELECT ''' + @DbName + ''' AS DbName
,''Fn'' AS ObjectType
,ROUTINE_NAME AS ObjectName
,ROUTINE_DEFINITION AS ObjectDefinition
FROM ' + QUOTENAME(@DbName) + '.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ''%'' + ''' + @strFind + ''' + ''%''
AND ROUTINE_NAME LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
AND ROUTINE_TYPE = ''FUNCTION''
ORDER BY ROUTINE_NAME
'
EXEC (@Sql)
END
END
SET @DbName = ''
--TO FIND STRING IN ALL TABLES OF DATABASE.
IF @ObjectType LIKE '%Tb%'
OR @ObjectType IS NULL
BEGIN
WHILE @DbName IS NOT NULL
BEGIN
SET @DbName = (
SELECT MIN(DbName)
FROM @DatabaseTable
WHERE DbName > @DbName
)
SET @Sql = '
USE ' + QUOTENAME(@DbName) + ';
INSERT INTO #Result (
DbName
,ObjectType
,ObjectName
,ObjectDefinition
)
SELECT ''' + @DbName + ''' AS DbName
,''Tb'' AS ObjectType
,t.NAME AS ObjectName
,c.NAME AS ObjectDefinition
FROM ' + QUOTENAME(@DbName) + '.sys.tables AS t
INNER JOIN ' + QUOTENAME(@DbName) + '.sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.NAME LIKE ''%'' + ''' + @strFind + ''' + ''%''
AND t.Name LIKE ''%'' + ''' + @ObjectName + ''' + ''%''
ORDER BY [ObjectDefinition] ASC
'
EXEC (@Sql)
END
END
SELECT DbName
,ObjectType
,ObjectName
,ObjectDefinition
FROM #Result
ORDER BY DbName ASC
,ObjectType ASC
,ObjectName ASC
DROP TABLE #Result
END
Like this:
Like Loading...
Tidbytez 