List all user databases the perform operation on each one

• workinonit

  Grasshopper

  Points: 20

  More actions

  January 24, 2012 at 6:13 am

  #391388

  I am trying to list all user databases in an instance then for each database look at one table and pull data from it. I have the sql to list all user databases but where I get stuck is trying to pass the database name to a select statement to select the data from the table in the database. Here is what I have so far:

  DECLARE @mycount int, @RecordCount int, @DBName Char(30)

  DECLARE @UserDatabaseNames TABLE (

  ID int,

  USERDBName char(30)

  INSERT INTO @UserDatabaseNames (ID, USERDBName)

  SELECT database_id, name FROM sys.databases

  WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

  SET @mycount = 1

  SET @RecordCount = (SELECT COUNT(*) FROM UserDatabaseNames)

  WHILE (@mycount <= @RecordCount)

  Here is where I don't know what to do. I want to take the name from UserDatabaseNames and then pass that into another select that will select a record from a table within the user database.

  Any help would be appreciated as I am a newbie to this.

• Lowell

  SSC Guru

  Points: 323482

  More actions

  January 24, 2012 at 6:22 am

  #1437630

  all the databases can be selected from master.sys.databases;

  SELECT * FROM master.sys.databases

  WHERE database_id > 4 --skip master/tempdb/model/msdb

  i would probably generate my list of commands from that same sql, and execute the string, or maybe use a cursor to look thru each database.

  an example:

  DECLARE @Commands varchar(max)

  SET @Commands =''

  SELECT @Commands = @Commands + 'ALTER DATABASE ' + quotename(name) + ' SET AUTO_CLOSE OFF;'+ CHAR(13) + CHAR(10)

  from master.sys.databases

  WHERE database_id > 4 --skip master/tempdb/model/msdb

  print @Commands

  --EXEC(@Commands)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• savethytrees

  SSCommitted

  Points: 1968

  More actions

  January 24, 2012 at 9:48 am

  #1437780

  I use the following TSQL to run commands against all databases. Change the SELECT statement to select the databases you need.

  Take a look at this script but do not run this TSQL in a SP.

  http://saveadba.blogspot.com/2011/10/sql-server-execute-same-query-against.html

  Blog
  http://saveadba.blogspot.com/

• workinonit

  Grasshopper

  Points: 20

  More actions

  January 24, 2012 at 12:09 pm

  #1437885

  This worked great with the few changes I needed to make.

  "I use the following TSQL to run commands against all databases. Change the SELECT statement to select the databases you need.

  Take a look at this script but do not run this TSQL in a SP.

  http://saveadba.blogspot.com/2011/10/sql-server-execute-same-query-against.html "

  Thanks