Query number of records across all databases

Question

I have multiple databases in my SQL Server. All databases are the same in structure but have different data. These databases are used to store sensor data so each sensor has it's own seperate DB in the SQL Server.

I want a query to Select the Database name and number of records in a specific table of each DB.

I tried with a cursor. I get error saying the name {query} is not a valid identifier. My Cursor is as follows:

Declare @dbname Varchar (50), @sql Varchar(1000)

Declare db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name LIKE 'EP505-%' -- All sensors of EP505

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sql= 'SELECT Count(*) FROM [' + @dbname + '].dbo.TimeLine'
   EXEC  @sql
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

In the output I require the db name and the number of records for the TimeLine table.

What's the best way to achieve what I am trying.

Try `EXEC (@sql)` otherwise SQL Server will interpret your `@sql` contents as a stored procedure or user-defined function rather than a query string. — Jonathon Ogden, Jul 19 '16 at 06:50
Possible duplicate of [exec failed because the name not a valid identifier?](http://stackoverflow.com/questions/11991987/exec-failed-because-the-name-not-a-valid-identifier) — Jonathon Ogden, Jul 19 '16 at 07:02

score 2 · Answer 1 · answered Jul 19 '16 at 06:58

2

Use parentheses when executing a SQL query string like so: EXEC (@sql). Without parentheses, SQL Server will interpret @sql as a stored procedure or user-defined function.

answered Jul 19 '16 at 06:58

Jonathon Ogden

1,562
13
19

score 0 · Answer 2 · answered Jul 19 '16 at 07:04

0

your attempt looks quite good so far.

Please try adding a fetch next below the exec-line and try putting the @SQL variable after the exec brackets. That worked in my SQL Server environment.

hope this helps

br Patrik

answered Jul 19 '16 at 07:04

pbo

31
1

score 0 · Answer 3 · answered Jul 19 '16 at 07:11

You can use sp_executeSQL to execute your dynamic query instead of exec statement which will help you to solve your issue

Here is the modified version

Declare @dbname Varchar (50), @sql nVarchar(1000)

Declare db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name LIKE 'kodyaz' -- All sensors of EP505

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sql= N'SELECT Count(*) FROM [' + @dbname + '].dbo.Kontaktpersonen'
   exec sp_executesql @sql
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

I change @sql data type to nvarchar() and use

exec sp_executesql @sql

Query number of records across all databases

3 Answers3