This VBS Script can be used to list or enumerate all of the column names for a specified table much like the previous SQL Query post SQL Query To Locate A Specified SQL Database Table Or Column Name and the previous VBS post VBS Script To Locate A Specified SQL Database Table Or Column Name. The results will be written to an excel spreadsheet that can be saved or printed for your reference.
Tip: Rather than pre-populating the input dialog boxes I took the liberty of adding comments next to the appropriate dialog boxes as a guide.
VBS Script:
strServer = InputBox ("Enter SQL Server Name") 'SQL Server
strDatabase = InputBox ("Enter Database Name") 'SMS_XXX
strTableName = InputBox ("Enter Table Name") 'Operating_System_Data
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
intRow = 2
objExcel.Cells(1, 1).Value = "Table Name"
objExcel.Cells(1, 2).Value = "Column Name"
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB;Data Source =" & strServer & ";" & _
"Trusted_Connection=Yes;Initial Catalog =" & strDatabase
Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open "Select SysObjects.Name T_Name, SysColumns.Name C_Name" & _
" From SysReferences" & _
" Right Outer Join SysColumns " & _
" Join SysTypes on SysColumns.XType = SysTypes.XType " & _
" Join SysObjects on SysColumns.Id = SysObjects.Id" & _
" On SysReferences.ConstId = SysObjects.Id" & _
" Where SysObjects.XType = 'U'" & _
" And SysObjects.Name = " & "'" & strTableName & "'", objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
objExcel.Cells(intRow, 1).Value = objRecordSet.Fields("T_Name").Value
objExcel.Cells(intRow, 2).Value = objRecordSet.Fields("C_Name").Value
objRecordSet.MoveNext
intRow = intRow + 1
Loop
objExcel.Range("A1:B1").Select
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
Set objRange = objExcel.Range("A1")
objRange.Sort objRange,1,,,,,,1
MsgBox "Done"
No Comments