info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



Use SQLCMD Mode to connect to numerous SQL Instances & loop through all databases & execute query
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-06-06









Solution:


Step 1: SSMS..Query..Select SQLDMD Mode


Step 2: SSMS..New Query..Execute the T-SQL below w/ required SQL Instance Names(s)


:CONNECT SQLInstanceName

set nocount on

Declare @srv_name varchar(150)

Set @srv_name = 'SQLInstanceName'

Declare @db_name varchar(250)

Declare c_db_processing Cursor For

Select [name] from master.sys.databases where state_desc = 'ONLINE'
order by name asc

Open c_db_processing
Fetch c_db_processing INTO @db_name
While @@Fetch_Status = 0
Begin
EXEC ('USE ['+ @db_name + ']' + 'Select ' + '''' + 'SYSOBJECTS' + '''' +' as TableName' + ',count(*) as TotalRecords,' + '''' + @srv_name + '''as ServerName,' + '''' + @db_name + '''as DatabaseName' + ' from dbo.sysobjects with (nolock) where xtype = ''U'' and category = 0')
Fetch c_db_processing Into @db_name
End

Close c_db_processing
Deallocate c_db_processing
GO



Resources:

Edit SQLCMD Scripts with Query Editor