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)
No comments:
Post a Comment