Earlier today I was thinking: I adopted my dog and taught him to sit, stay, lay down, and come. In return he taught me unconditional love, how to live life and be happy, how to forgive, how to be a friend, and how to be true and faithful.
Latest Entries »
I found this stored procedure written by Michael F. Berry and then later modified by Bill Lescher and Chase Jones on some SQL Server related website (can’t remember which one anymore). It needed some work to be perfect, and so I made the changes and here it is for you to implement on you database servers. Just create it in the master database and call it from any user/system database.
To get the code below, just hover your mouse over the code, and on the top right corner of the window, 3 options are presented to you.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_Find2] Script Date: 04/18/2012 13:40:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[sp_Find2]
@SearchText1 varchar(100) = ''
, @SearchText2 varchar(100) = ''
, @SearchText3 varchar(100) = ''
, @SearchText4 varchar(100) = ''
, @DBName sysname = Null
-- , @PreviewTextSize int = 200
, @SearchDBsFlag char(1) = 'Y'
, @SearchJobsFlag char(1) = 'Y'
, @SearchSSISFlag char(1) = 'Y'
As
/*
* Created: 12/19/06, Michael F. Berry (SQL Server Magazine contributor)
*
* Modified: 01/25/07, Michael F. Berry, Make it output to one main recordset for clarity
* Modified: 09/04/08, Bill Lescher and Chase Jones, Updated for SQL2005 and added Jobs & SSIS Packages
* Modified: 07/22/09, Bill L, Returning the PreviewText
* Modified: 04/17/12, Said Khorramshahgol, Enabled searching for Tables & Columns
* Modified: 04/17/12, Said Khorramshahgol, Enabled searching for up to 4 keywords
* Modified: 04/17/12, Said Khorramshahgol, PreviewText column now shows column size for columns
* Modified: 04/17/12, Said Khorramshahgol, Enabled search in Master and MSDB databases
* Modified: 04/17/12, Said Khorramshahgol, PreviewText column size is now set at 200 characters
*
* Description: Find any string within the T-SQL code on this SQL Server instance, specifically
* Database objects and/or SQL Agent Jobs and/or SSIS Packages
*
* Test: sp_Find4 'KEYWORD 1' -- Search for 1 keyword across all objects/DBs/SSIS/Jobs
* sp_Find4 'KEYWORD 1', 'KEYWORD2', 'KEYWORD3', 'KEYWORD4' -- Search for 2 keywords across all objects/DBs/SSIS/Jobs
* sp_Find4 'track', NULL, NULL, NULL, 'Common' -- Search "Common" DB, Jobs, and SSIS packages
* sp_Find4 'track', NULL, NULL, NULL, 'Common', 'Y', 'N', 'N' --DB Only
* sp_Find4 'track', NULL, NULL, NULL, 'Common', 'N', 'N', 'Y' --SSIS Only
*/
Set Transaction Isolation Level Read Uncommitted;
Set Nocount On;
DECLARE @PreviewTextSize int
SET @PreviewTextSize = 200
Create Table #FoundObject (
DatabaseName sysname
, ObjectName sysname
, ObjectTypeDesc nvarchar(60)
, PreviewText varchar(max))--To show a little bit of the code and other info
Declare @SQL as nvarchar(max);
/**************************
* Database Search
***************************/
If @SearchDBsFlag = 'Y'
Begin
If @DBName Is Null --Loop through all normal user databases
Begin
Declare ObjCursor Cursor Local Fast_Forward For
Select [Name]
From Master.sys.Databases
Where [Name] Not In ('AdventureWorks', 'AdventureWorksDW', 'Distribution', 'Model', 'TempDB')
-- ('AdventureWorks', 'AdventureWorksDW', 'Distribution', 'Master', 'MSDB', 'Model', 'TempDB');
Open ObjCursor;
Fetch Next From ObjCursor Into @DBName;
While @@Fetch_Status = 0
Begin
Select @SQL = '
Use [' + @DBName + ']
Insert Into #FoundObject (
DatabaseName
, ObjectName
, ObjectTypeDesc
, PreviewText)
Select Distinct
''' + @DBName + '''
, sch.[Name] + ''.'' + obj.[Name] as ObjectName
, obj.Type_Desc
, Replace(Replace(SubString(mod.Definition, CharIndex(''' + @SearchText1 + ''', mod.Definition) - ' + Cast(@PreviewTextSize / 2 As varchar) + ', ' +
Cast(@PreviewTextSize As varchar) + '), char(13) + char(10), ''''), ''' + @SearchText1 + ''', ''***' + @SearchText1 + '***'')
From sys.objects obj
Inner Join sys.SQL_Modules mod On obj.Object_Id = mod.Object_Id
Inner Join sys.Schemas sch On obj.Schema_Id = sch.Schema_Id
Where mod.Definition Like ''%' + @SearchText1 + '%''
AND mod.Definition Like ''%' + COALESCE(@SearchText2, '') + '%''
AND mod.Definition Like ''%' + COALESCE(@SearchText3, '') + '%''
AND mod.Definition Like ''%' + COALESCE(@SearchText4, '') + '%''';
-- Order By ObjectName';
Exec dbo.sp_executesql @SQL;
Select @SQL = '
Use [' + @DBName + ']
Insert Into #FoundObject (
DatabaseName
, ObjectName
, ObjectTypeDesc
, PreviewText)
Select TABLE_CATALOG, TABLE_SCHEMA+''.''+TABLE_NAME, TABLE_TYPE, ''Table definitions are not set up yet''
From information_schema.tables
Where TABLE_NAME like ''%' + @SearchText1 + '%''
AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText2, '') + '%''
AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText3, '') + '%''
AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText4, '') + '%''';
Exec dbo.sp_executesql @SQL;
Select @SQL = '
Use [' + @DBName + ']
Insert Into #FoundObject (
DatabaseName
, ObjectName
, ObjectTypeDesc
, PreviewText)
Select TABLE_CATALOG, TABLE_SCHEMA+''.''+TABLE_NAME+''.''+COLUMN_NAME, ''COLUMN'', DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(100)) + '')''
From information_schema.columns
Where COLUMN_NAME like ''%' + @SearchText1 + '%''
AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText2, '') + '%''
AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText3, '') + '%''
AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText4, '') + '%''';
Exec dbo.sp_executesql @SQL;
Fetch Next From ObjCursor Into @DBName;
End;
Close ObjCursor;
Deallocate ObjCursor;
End
Else --Only look through given database
Begin
Select @SQL = '
Use [' + @DBName + ']
Insert Into #FoundObject (
DatabaseName
, ObjectName
, ObjectTypeDesc
, PreviewText)
Select Distinct
''' + @DBName + '''
, sch.[Name] + ''.'' + obj.[Name] as ObjectName
, obj.Type_Desc
, Replace(Replace(SubString(mod.Definition, CharIndex(''' + @SearchText1 + ''', mod.Definition) - ' + Cast(@PreviewTextSize / 2 As varchar) + ', ' +
Cast(@PreviewTextSize As varchar) + '), char(13) + char(10), ''''), ''' + @SearchText1 + ''', ''***' + @SearchText1 + '***'')
From sys.objects obj
Inner Join sys.SQL_Modules mod On obj.Object_Id = mod.Object_Id
Inner Join sys.Schemas sch On obj.Schema_Id = sch.Schema_Id
Where mod.Definition Like ''%' + @SearchText1 + '%''
AND mod.Definition Like ''%' + COALESCE(@SearchText2, '') + '%''
AND mod.Definition Like ''%' + COALESCE(@SearchText3, '') + '%''
AND mod.Definition Like ''%' + COALESCE(@SearchText4, '') + '%''';
Exec dbo.sp_ExecuteSQL @SQL;
End;
Select 'Database Objects' As SearchType;
Select
DatabaseName
, ObjectName
, ObjectTypeDesc As ObjectType
, PreviewText
From #FoundObject
Order By DatabaseName, ObjectName;
End
/**************************
* Job Search
***************************/
If @SearchJobsFlag = 'Y'
Begin
Select 'Job Steps' As SearchType;
Select j.[Name] As [Job Name]
, s.Step_Id As [Step #]
, Replace(Replace(SubString(s.Command, CharIndex(@SearchText1, s.Command) - @PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SearchText1, '***' + @SearchText1 + '***') As Command
From MSDB.dbo.sysJobs j
Inner Join MSDB.dbo.sysJobSteps s On j.Job_Id = s.Job_Id
Where s.Command Like '%' + @SearchText1 + '%'
AND s.Command Like '%' + COALESCE(@SearchText2, '') + '%'
AND s.Command Like '%' + COALESCE(@SearchText3, '') + '%'
AND s.Command Like '%' + COALESCE(@SearchText4, '') + '%';
End
/**************************
* SSIS Search
***************************/
If @SearchSSISFlag = 'Y'
Begin
Select 'SSIS Packages' As SearchType;
Select [Name] As [SSIS Name]
, Replace(Replace(SubString(Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)), CharIndex(@SearchText1, Cast(Cast(PackageData As varbinary(Max)) As varchar(Max))) -
@PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SearchText1, '***' + @SearchText1 + '***') As [SSIS XML]
From MSDB.dbo.sysDTSPackages90
Where Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + @SearchText1 + '%'
AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText2, '') + '%'
AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText3, '') + '%'
AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText4, '') + '%';
End
GO
I have finally joined LinkedIn.com and here’s my profile:
Here’s a simple query that will list all indexes that have been created on the data filegroup, instead of the index filegroup(s).
SELECT distinct obj.[name] as [table name], obj.[type_desc] as [object type],
i.[name] as [index name], i.[index_id], f.[name] as [filegroup name], ((prt.used_page_count * 8 ) / 1024) as [Size (MB)]
FROM sys.indexes i, sys.filegroups f, sys.objects obj, sys.dm_db_partition_stats prt, sys.partitions p
where i.data_space_id = f.data_space_id and
i.object_id = obj.object_id and
p.object_id = i.object_id and
p.index_id = i.index_id and
p.object_id = prt.object_id and
p.index_id = prt.index_id and
p.partition_number = prt.partition_number and
i.name is not null and
i.type_desc in (‘HEAP’, ‘NONCLUSTERED’) and
obj.[type] in (‘U’, ‘V’) and
i.data_space_id = 1 — Filegroup
order by 1, 3
