Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 19 January 2016

SP_MSforeachDB using Filter To get all object who is having text inside objects

SQL Server: Applying Filter on sp_MSforeachDB


Working on multiple databases on a single instance, sometime you need to execute a query for each database and for that sp_MSforeachdb is the best choice.
Recently talking to my development team I came to know that a very few guys have idea about filter for sp_MSforeachDB.

For example, if I need to get database physical files information for each database on my instance, I will use following simple query
EXEC sp_MSforeachdb '
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'


BUT what if, I need to omit MSDB, TempDB and Model databases for this query. Now I have to apply filter. This can be achieved by simple IF statement.
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'

You can even use ? sign in WHERE clause.
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
       WHERE name  LIKE ''?%'' -- Only Files starting with DB name
END'

Output can be saved in tables (user, temporary) or table variables
DECLARE   @DatabasesSize TABLE
    (
      name VARCHAR(50),
      physical_name VARCHAR(500),
      state BIT,
      size INT
    )

INSERT  INTO @DatabasesSize
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'


---------------To get all object who is having text inside objects---------------
declare @SQL nvarchar(max)
 
set @SQL = ''

select @SQL = @SQL + CHAR(13) + 'USE ' + QUOTENAME([name]) + ';
SELECT ' +quotename([name],'''') + 'as [Database Name], so.name AS [Table Name],so.xtype
FROM syscomments c   
    join sysobjects AS so on c.id = so.id   
WHERE c.TEXT LIKE ''%test%''   ---------text to find inside code
    ' from sys.databases  
 
execute (@SQL)