0

I am trying to audit the network, to find and clean out old databases that may have been tested and forgotten.

Right now, I know I can use SQLCMD -L to see the servers and instances. I can use exec sp_helpdb to get the Server name, name of the db, size, owner, created, and status if Management Studio is connected to the server(s).

Both of these on their own is great, but they require connecting to each server in MGMT Studio, then running the below query against a Registered Server Group.

I was hoping to create a loop of the following query to go through all the databases that SQLCMD -L has found, so that I can send out audit results for cleanup, possibly through SQLCMS/OSQL, to avoid the manual connection process. I also may have to work out the appropriate log on account to them, so if there's option for that in this mess, that would be great as well (Sa, Win Auth, SQL Auth, etc.)

If anyone has some suggestion on solution to run it as an automated process, it would be greatly appreciated. 

create table #temp
(
name sysname
,db_size nvarchar(13)
,owner sysname
,dbid smallint
,created smalldatetime--nvarchar(11)
,status nvarchar(600)
,compatibility_level tinyint
)
go
insert into #temp
exec sp_helpdb

ALTER TABLE #temp
DROP COLUMN dbid
ALTER TABLE #temp
DROP COLUMN status
ALTER TABLE #temp
DROP COLUMN compatibility_level


select * from #temp
where name not like'master%'
AND name not like 'model%'
AND name not like 'msdb%'
AND name not like 'ems_master%'
AND name not like 'tempdb%'
AND name not like 'ReportServer%'
AND name not like 'HR'
AND name not like 'EMS_Personnel%'
order by created
drop table #temp
Billie Hawkins
  • 166
  • 2
  • 11

2 Answers2

0

If you save that query as batch.sql, then you can open a Powershell window, cd to the directory where batch.sql is, and then run this:

sqlcmd -L | ? {$_ -ne "Servers:"} | ? {$_ -ne ""} | % { sqlcmd -S $_ -b -i batch.sql}

That will run sqlcmd -L for you, filter out the lines that aren't server names, and then connect to each server and run the T-SQL code in batch.sql. If you need to add a username and password, you can the -U and -P flags to the second sqlcmd command.

dpw
  • 1,576
  • 9
  • 14
0
for /f "tokens=*" %%i in ('SQLCMD -L') do (
Echo Working on %%i
SQLCMD -E -S %%i -Q "exec sp_helpdb" -o "C:\Temp\BillieTest\ServerDatabases.csv" -s"," -W -w 999
Echo %%i Processed. 
)

I was able to use this to loop the command. it doesn't solve the issue with multiple logins, but I will work on getting a unified windows auth login to run this on.

Thank you for those who viewed, and I still welcome any better options for this process! :)

Billie Hawkins
  • 166
  • 2
  • 11