Gathering db names then for each name running a command

[br0ck]

Hi,

Im kinda new to all this scripting stuff

So if you see mistakes please point them out ….

I have 3 scripts I modified from Microsoft’s site
2 i already merged together

First one gathers all SQL db names and dumps them to file

Option Explicit

Dim strComputer
Dim	objSQLServer
Dim colDatabases
Dim objDatabase
Dim w
Dim ws
Dim WshShell

const ForReading = 1
const ForWriting = 2
const ForAppending = 8
Const TristateFalse = 0

	If (strComputer = "") Then
		strComputer = InputBox("What SQL Server do you want to read (default=localhost)","Select Target",".")
	End If
	
Set ws = CreateObject ("Scripting.FileSystemObject")
Set w = ws.OpenTextFile ("T:\SQLServers\SQLServerDatabases.txt", ForAppending, True)
Set WshShell = WScript.CreateObject("WScript.Shell")
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
		
objSQLServer.LoginSecure = True
objSQLServer.Connect strComputer

Set colDatabases = objSQLServer.Databases



For Each objDatabase In colDatabases
     w.WriteLine objDatabase.Name
Next
WScript.Sleep 6000

WshShell.Run "T:\SQLServers\sql.vbs",1,True

Then the next reads that file and creates a file for each database with the db file information and user info

'Option Explicit

Dim strComputer
Dim	objSQLServer
Dim colDatabases
Dim objDatabase
Dim w
Dim ws
Dim strDBName
Dim objDB
Dim colUsers

const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TristateFalse = 0
Const HARD_DISK = 3

	strComputer = "."
		
'====

Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")

Set objDictionary = CreateObject("Scripting.Dictionary")
Set objArgs = WScript.Arguments


Set objTextFile = objFSO.OpenTextFile("T:\SQLServers\SQLServerDatabases.txt", ForReading)

CutOff = Date() - 1

'==Extract arguments from text file==
i = 0
Do While objTextFile.AtEndOfStream <> True
strNextLine = objTextFile.Readline
objDictionary.Add i, strNextLine
i = i + 1
Loop


'==Assign each item in the text file to the Dictionary object==
For Each objItem in objDictionary

'===Assign each item in Dictionary to strComputer variable===
strDBName = objDictionary.Item(objItem)


	
Set ws = CreateObject ("Scripting.FileSystemObject")
Set w = ws.OpenTextFile ("T:\SQLServers\sqlinfo\SQLdb_" & strDBName & ".txt", ForAppending, True)

Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True
objSQLServer.Connect strComputer


Set objDB = objSQLServer.Databases(strDBName)
Set colUsers = objDB.Users
 w.WriteLine " "
 w.WriteLine "====================================== "
 w.WriteLine "  Database Name = " & Date 
 w.WriteLine "  Database Name = " & strDBName
 w.WriteLine "====================================== "

	w.WriteLine " " 
	w.WriteLine "Total Size of Data File + Transaction Log of DB " & strDBName & ": " & objDB.Size & "(MB)"
	w.WriteLine " "
	w.WriteLine "Space Left (Data File + Transaction Log) for DB " &_
			 strDBName & ": " & objDB.SpaceAvailableInMB & "(MB)"
			 w.WriteLine " "
	 w.WriteLine "============================= "
	 w.WriteLine "==    -Database Users-     == " 
	 w.WriteLine "============================= "
For Each objUser In colUsers
    w.WriteLine " "
   	w.WriteLine " --==-- "
    w.WriteLine "User: "    & objUser.Name & ", " & "Login: "   & objUser.Login
   

 
Next     
Next

I am having a hard time merging this in to one script

I cant figure out how to have the script read the names of the databases inside the script then apply that to the tasks I want done

It works now reading from the file but I would like to remove that step

Tia

Br0ck

 

[tsuji]

I just cut and paste. Keywords are : trace the variables, know what they are and what they store. Then, it is eliminate, eliminate, eliminate, eliminate all the cosmetic and redundancies and pseudo-professional-look. And add things which are important but curiously neglected. You have to find out that they mean in practice yourself.
[tt]
Option Explicit

Dim strComputer : strComputer=""
Dim objSQLServer
Dim colDatabases
Dim objDatabase
Dim colUsers
Dim objUser

Dim w
Dim ws

const ForAppending = 8

If (strComputer = "") Then
strComputer = InputBox("What SQL Server do you want to read (default=localhost)","Select Target",".")
End If

Set ws = CreateObject ("Scripting.FileSystemObject")
Set objSQLServer = CreateObject("SQLDMO.SQLServer")

objSQLServer.LoginSecure = True
objSQLServer.Connect strComputer

Set colDatabases = objSQLServer.Databases

For Each objDatabase In colDatabases
Set w = ws.OpenTextFile ("T:\SQLServers\sqlinfo\SQLdb_" & objDatabase.name & ".txt", ForAppending, True)
Set colUsers = objDatabase.Users
w.WriteLine " "
w.WriteLine "====================================== "
w.WriteLine " Database Name = " & Date
w.WriteLine " Database Name = " & objDatabase.name
w.WriteLine "====================================== "

w.WriteLine " "
w.WriteLine "Total Size of Data File + Transaction Log of DB " & objDatabase.name & ": " & objDatabase.Size & "(MB)"
w.WriteLine " "
w.WriteLine "Space Left (Data File + Transaction Log) for DB " &_
strDBName & ": " & objDatabase.SpaceAvailableInMB & "(MB)"
w.WriteLine " "
w.WriteLine "============================= "
w.WriteLine "== -Database Users- == "
w.WriteLine "============================= "
For Each objUser In colUsers
w.WriteLine " "
w.WriteLine " --==-- "
w.WriteLine "User: " & objUser.Name & ", " & "Login: " & objUser.Login
Next
w.close
set w=nothing
set colUsers=nothing
Next
set colDatabases=nothing
set objSQLServer=nothing
set ws=nothing

'====
[/tt]