-2

I have the below script that works fine & fetches all the results I need. However It does it only for the current DB context. I have several hundred databases in my instance and need to get the results for all databases in a single execution.
How do I modify the script to make it work for all Databases.

IF OBJECT_ID('tempdb..#AllInfo', N'U') IS NOT NULL DROP TABLE #AllInfo;

CREATE TABLE #AllInfo (
dbase_name nvarchar(200),
tbl_name nvarchar(300),
column_name nvarchar(300),
min_date datetime
)
DECLARE @Statement varchar(2000);
DECLARE cr_MaxDateTime CURSOR LOCAL FOR
    SELECT 'SELECT DB_NAME(), ' + '''' + TAB.name + ''''  + ', ' + '''' + COL.name + '''' +' ,MIN([' + COL.name +']) AS ''Min' + COL.name + '_' + TAB.name + ''' FROM [' + SCH.name + '].[' + TAB.name + ']'
    FROM   sys.schemas AS SCH
    INNER  JOIN sys.tables AS TAB
           ON TAB.schema_id = SCH.schema_id
    INNER  JOIN sys.columns AS COL
           ON COL.object_id = TAB.object_id
    INNER  JOIN sys.types AS UDT
           ON COL.user_type_id = UDT.user_type_id
    INNER  JOIN sys.types AS TYP
           ON TYP.system_type_id = UDT.system_type_id AND
              TYP.user_type_id = TYP.system_type_id   AND
              TYP.name IN ('date', 'datetime2', 'datetimeoffset', 'datetime', 'time')
    ORDER  BY SCH.name, TAB.name, COL.name
FOR READ ONLY;
OPEN cr_MaxDateTime;
FETCH cr_MaxDateTime INTO @Statement;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Statement;
    INSERT INTO #AllInfo
    EXECUTE(@Statement);
    FETCH cr_MaxDateTime INTO @Statement;
END;
CLOSE cr_MaxDateTime;
DEALLOCATE cr_MaxDateTime;

select * from #AllInfo

I know sp_MSForEachDB may help, but the examples I could find are mostly single line commands. I couldn't make it work for the whole script.

Improve this question
4
  • Does this answer your question? SQL Server : run a script on all databases Commented Feb 20, 2023 at 10:15
  • 1
    Does this answer your question? Executing SQL query on multiple databases Commented Feb 20, 2023 at 10:16
  • Re: sp_msforeachdb, It's no different for a long script, just create a big long string with your whole thing and attach ?. to every table so it goes to correct database or add a USE ?. It's kind of a hassle though, since you usually have to replace a lot of quotes etc to make it work. Commented Feb 20, 2023 at 10:23
  • I did try sp_msforeachdb. As you can see, there are already quite a lot of quotes in the existing script. I just couldn't make it work after replacing all the quotes. It just giving me some or the other error. Commented Feb 20, 2023 at 11:00

1 Answer 1

Reset to default
0

Assuming you are using a modern and supported version of SQL Server, a simpler way to drop the #temp table (unnecessary of course if you put this code into a stored procedure):

DROP TABLE IF EXISTS #AllInfo;

CREATE TABLE #AllInfo 
(
  dbase_name  nvarchar(130),
  tbl_name    nvarchar(260),
  column_name nvarchar(130),
  min_date    datetime
);

Now, here's one way you can take a base SQL statement, and conditionally execute that dynamic SQL in each database. I did my best to avoid single quotes but sometimes that actually makes things less readable.

DECLARE @base   nvarchar(max) = N'SELECT DB_NAME(),
                                  CONCAT($schn$, char(46), $tbn$), 
                                  $coln$, MIN($col$) FROM $sch$.$tb$;',
    @nested nvarchar(max) = N'DECLARE @sql nvarchar(max) = SPACE(0);
SELECT @sql += REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@base, 
  N''$sch$'', QUOTENAME(s.name)), N''$schn$'', QUOTENAME(s.name, char(39))), 
  N''$tb$'',  QUOTENAME(t.name)), N''$tbn$'',  QUOTENAME(t.name, char(39))), 
  N''$col$'', QUOTENAME(c.name)), N''$coln$'', QUOTENAME(c.name, char(39)))
FROM sys.schemas AS s
INNER JOIN sys.tables AS t  ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns AS c ON t.[object_id] = c.[object_id]
WHERE EXISTS (
  SELECT 1 FROM sys.types 
  WHERE system_type_id IN (40,41,42,43,61) 
  AND user_type_id = c.user_type_id);
INSERT #AllInfo EXEC sys.sp_executesql @sql;';

DECLARE @context nvarchar(2000), @c cursor;

SET @c = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR 
         SELECT QUOTENAME(name) + N'.sys.sp_executesql'
         FROM sys.databases WHERE state = 0 AND database_id > 4;

OPEN @c; FETCH NEXT FROM @c INTO @context;

WHILE @@FETCH_STATUS <> -1
BEGIN
  EXEC @context @nested, N'@base nvarchar(max)', @base;
  FETCH NEXT FROM @c INTO @context;
END

Basically, we need to prepare a statement that can be run in each database and then, inside each database, for each column that matches our criteria. So we have nested dynamic SQL that gets custom-built inside the database cursor, replacing tokens like $sch$ with the actual schema name, $tb$ with the actual table name, and $col$ with the actual column name. The REPLACE() is messy, and I initially went that route because I was referencing each token more than once, but it got more complicated because in some places it needed to be rendered as a string literal instead of a bracketed entity name.

Finally:

SELECT * FROM #AllInfo;

As an aside, please stay away from sp_MSforeachdb. This procedure is undocumented and unsupported for a reason: it is buggy and skips databases without any warning. It also forces you to reference the database with ? which can be tedious and even problematic. I wrote a replacement here, which avoids the bug, and also prevents you from littering your dynamic SQL commands with ?. You can read more about it in the following posts (and not even all are mine):

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Hello @Aaron Bertrand, Many Thanks and I really appreciate the time you spent on writing the code & explaining it in detail along with references. I'm still trying to wrap my head around it & understand it better [partly due to my own inexperience :) ]. In the meantime, I did execute the code in my environment & it is somehow limiting the results that are fetched, as in when I execute the original script for 1 database, It gives me over 100 Col with the min_date. However your above script is only fetching 43 records for all databases together. Is there any filter in there limiting the results?
@VineethNair I spotted the issue, try the updated query.
After comparing the results, it looks like the script is giving 1 row of output per database. So originally if my database has over 100 cols (across multiple tables) that match the criteria, instead of putting out 100 x No. of DBs (43 in this case). It just gives me 43 rows of output. The cols also seems to be randomly chosen for each DB.
Sorry I missed your edit to the query. It works fine now. Thank you again :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.