Latest Entries »

My dog is my teacher

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.

My dog

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
 

My LinkedIn Profile

I have finally joined LinkedIn.com and here’s my profile:

http://www.linkedin.com/pub/said-khorramshahgol/43/bba/a08

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: