How to run the same query on all the databases on an instance?

by

Try this one –

    SET NOCOUNT ON;
    
    IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
       DROP TABLE #temp
    
    CREATE TABLE #temp
    (
          [COUNT] INT
        , DB VARCHAR(50)
    )
    
    DECLARE @TableName NVARCHAR(50) 
    SELECT @TableName="[dbo].[CUSTOMERS]"
    
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = STUFF((
        SELECT CHAR(13) + 'SELECT ' + QUOTENAME(name, '''') + ', COUNT(1) FROM ' + QUOTENAME(name) + '.' + QUOTENAME(@TableName)
        FROM sys.databases 
        WHERE OBJECT_ID(QUOTENAME(name) + '.' + QUOTENAME(@TableName)) IS NOT NULL
        FOR XML PATH(''), TYPE).value('text()[1]', 'NVARCHAR(MAX)'), 1, 1, '')
    
    INSERT INTO #temp (DB, [COUNT])              
    EXEC sys.sp_executesql @SQL
    
    SELECT * 
    FROM #temp t

Output (for example, in AdventureWorks) –

COUNT       DB
----------- --------------------------------------------------
19972       AdventureWorks2008R2
19975       AdventureWorks2012
19472       AdventureWorks2008R2_Live

More Related Contents:

  1. Create a view with ORDER BY clause
  2. SQL Update with row_number()
  3. How to write UTF-8 characters using bulk insert in SQL Server?
  4. Is a single SQL Server statement atomic and consistent?
  5. Connecting to MS SQL Server with Windows Authentication using Python?
  6. determining the character set of a table / database?
  7. How to query for Xml values and attributes from table in SQL Server?
  8. A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233)
  9. Should every User Table have a Clustered Index?
  10. cross apply xml query performs exponentially worse as xml document grows
  11. Making an Entity Framework Model span multiple databases
  12. SQL Server Configuration Manager cannot be found
  13. Compute MD5 hash of a UTF8 string
  14. Sql Server replication requires the actual server name to make a connection to the server
  15. Convert a string with ‘YYYYMMDDHHMMSS’ format to datetime
  16. Convert from DateTime to INT
  17. replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)
  18. SQL Server 2008 Spatial: find a point in polygon
  19. Where clause not working in sql server
  20. Compare the values of column in SQL Server
  21. T-SQL stored procedure that accepts multiple Id values
  22. How do I escape a percentage sign in T-SQL?
  23. Get list of databases from SQL Server
  24. CTE to get all children (descendants) of a parent
  25. EXEC sp_executesql with multiple parameters
  26. SQL Server – Dynamic PIVOT Table – SQL Injection
  27. How to add custom attributes to SQL connection string?
  28. SSIS reading LF as terminator when its set as CRLF
  29. How do I remove non-breaking spaces from a column in SQL server?
  30. Why is casting from float to varchar being rounded in SQL Server?

Leave a Comment