1

I need help what statement or function i can use to run a query in all database using one script , i want to run the below script at the same time to retrieve output in all databases

   select 
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id), 
      OBJECT_NAME (id) as StoredProc,
      Text  
    from syscomments 
    where text like '%Janice\SQL%DTS%'.
0

3 Answers 3

3

The solution heavily depends on how much work you are willing to put in your initial query and what is going to consume the data (and how).

-- Option 1 --

If this is a one-off requirement, you can use variations of sp_MSForEachDB to retrieve a separate data set for each database.

Personally, I don't like sp_MsForEachDB for a number of reasons, mainly because it uses a wrongly configured cursor to enumerate the databases, which can make it skip databases at times. I coded my own alternative to it some years ago and blogged about it here

The main issue with this solution is that it doesn't return a single data set, which I suppose is what you want. In this case, you should create a temporary table upfront and use it to store data returned from your query. All executions against different databases will populate the temporary table with the data you need.

Here's an example:

DECLARE @sql nvarchar(max);

SET @sql = '
select 
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id), 
      OBJECT_NAME (id) as StoredProc,
      Text  
    from syscomments 
    where text like ''%Janice\SQL%DTS%''
';


CREATE TABLE #results (
    [ServerName] [nvarchar](128) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Object_ID] [int] NOT NULL,
    [StoredProc] [nvarchar](128) NULL,
    [Text] [nvarchar](4000) NULL
);


DECLARE @statement nvarchar(max);

SET @statement = (
SELECT 'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @sql; '
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)');


INSERT #results
EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql;

SELECT *
FROM #results;

-- Option 2 --

This solution requires that you put your sql statement in a string variable and you know the output of the query. If this is not acceptable for you, some time ago I coded a stored procedure that can take the SELECT statement inside a view and use it to execute against all databases (or a subset of your databases). In this case, you just need to create a view with the statement you want to run and pass the name to that stored procedure. You can find the code here.

-- Option 3 --

You could also UNION ALL the results that come from your query, something like this:

DECLARE @sql nvarchar(max)

SET @sql = '
select 
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id), 
      OBJECT_NAME (id) as StoredProc,
      Text  
    from <dbname>.dbo.syscomments 
    where text like ''%Janice\SQL%DTS%''
';


DECLARE @statement nvarchar(max);

SET @statement = STUFF((
SELECT 'UNION ALL ' + REPLACE(@sql,'<dbname>',QUOTENAME(name))
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)'),1,9,SPACE(0));

EXEC sp_executesql @statement

If you use this approach, you also have to take care of everything that could go wrong in the UNION ALL. Collation conflicts between databases, for instance, have to be resolved in the query.

Hope this helps.

BTW, syscomments is deprecated, so if you're using SQL Server 2005 and above, use sys.sql_modules instead.

0

the below script helped :) thanks

DECLARE @command varchar(1000) 
SELECT @command = 
    'USE [?] select 
      DB_NAME() as DatabaseName,
      @@SERVERNAME as ServerName,
      Object_ID = (id), 
      OBJECT_NAME (id) as StoredProc,
      Text  
    from syscomments where text like ''%Janice\SQL%DTS%'''
EXEC sp_MSforeachdb @command 
-2
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; 
BEGIN
select 
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id), 
      OBJECT_NAME (id) as StoredProc,
      Text  
    from syscomments 
    where text like '%Janice\SQL%DTS%'.
END
1
  • Please explain how this answers the question. A code fragment is usually not an answer. Mar 3, 2015 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.