概要
通过使用 SQL Server 分布式管理对象 (SQLDMO), 可列出本地网段上所有运行 SQL 服务器。
更多信息
请按照步骤来枚举, 本地网段上所有可用 SQL 服务器。
使用 VisualBasic
1. 启动新标准 EXE 工程。
2. 在 项目 菜单, 单击 引用 。 选择 Microsoft SQLDMO 对象库 , 并单击 确定 。
3. 将 CommandButton 和 ListBox 控件放到 Form 1。
4. 将以下代码添加到窗体模块:
Code
1![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
Private Sub Command1_Click()Sub Command1_Click()
2
Dim i As Integer
3
Dim oNames As SQLDMO.NameList
4
Dim oSQLApp As SQLDMO.Application
5
Set oSQLApp = New SQLDMO.Application
6
7
8
Set oNames = oSQLApp.ListAvailableSQLServers()
9
List1.Clear
10
For i = 1 To oNames.Count
11
List1.AddItem oNames.Item(i)
12
Next i
13
End Sub
14
使用Visual C++
1.启动新的空 Win 32 控制台应用程序。
2.向项目添加新 C++ 源文件并粘贴以下代码:
1
#define
UNICODE
2
#define
_UNICODE
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
#include
<
windows.h
>
5
#include
<
tchar.h
>
6
#include
<
stdio.h
>
7
#include
<
initguid.h
>
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
#include
"
C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\DEVTOOLS\INCLUDE\sqldmoid.h
"
10
#include
"
C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\DEVTOOLS\INCLUDE\sqldmo.h
"
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
int
main()
13
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
14
LPSQLDMOAPPLICATION pDMOApp = NULL;
15
LPSQLDMONAMELIST pNameList = NULL;
16![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
int iExitStatus = 1; // assume failure.
18![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
try
20![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
21
// Initialize COM.
22
//
23
if FAILED(CoInitialize (NULL))
24![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
25
return (iExitStatus);
26
}
27![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
29
// Create SQLDMOApplication.
30
if FAILED(CoCreateInstance(CLSID_SQLDMOApplication, NULL, CLSCTX_INPROC_SERVER, IID_IWSQLDMOApplication, (LPVOID*)&pDMOApp))
31![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
32
return (iExitStatus);
33
}
34
35
// Get the list of servers.
36
if SUCCEEDED(pDMOApp->ListAvailableSQLServers(&pNameList))
37![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
38
long lCount=0;
39
BSTR strName=NULL;
40
//retrieve the count
41
pNameList->GetCount(&lCount);
42
_tprintf(_T("There are %d SQL Servers active on the network.\n"),lCount );
43![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
44
for(long i=0; i < lCount; i++)
45![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
46
//get the server name
47
pNameList->GetItemByOrd(i, &strName);
48
_tprintf(_T("%s\n"),strName );
49
}
50
iExitStatus = 0;
51
}
52![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
53
if (pDMOApp)
54![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
55
pNameList = NULL;
56
pDMOApp->Release();
57
pDMOApp = NULL;
58
}
59
}
60
catch(
)
61![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
62
_tprintf(TEXT("error dmolistservers unhandled exception (%d)\n"), GetLastError() );
63
}
64![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
65
return iExitStatus;
66
}
67
使用Visual C#
Code
1![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
得到所有本地网络中可使用的SQL服务器列表#region 得到所有本地网络中可使用的SQL服务器列表
2![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
3
/// 得到所有本地网络中可使用的SQL服务器列表
4
/// </summary>
5
/// <param name="p_strServerList">服务器列表</param>
6
/// <returns></returns>
7
public static bool GetServers(ref string [] p_strServerList)
8![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
9
try
10![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
11
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
12
SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
13
if(sqlServers.Count > 0)
14![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
15
p_strServerList = new string[sqlServers.Count];
16
for(int i=0;i<sqlServers.Count;i++)
17![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
18
string srv = sqlServers.Item(i + 1);
19
if(srv != null)
20![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
21
p_strServerList[i] = srv;
22
}
23
}
24
}
25
return true;
26
}
27
catch(Exception ex)
28![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
29
throw ex;
30
}
31
}
32
33
#endregion
34
35![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
得到指定SQL服务器所有数据库的列表#region 得到指定SQL服务器所有数据库的列表
36![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
37
/// 得到指定SQL服务器所有数据库的列表
38
/// </summary>
39
/// <param name="p_strDataBaseList">数据库列表</param>
40
/// <param name="p_strServer">服务器名</param>
41
/// <param name="p_strUser">用户名</param>
42
/// <param name="p_strPWD">密码</param>
43
/// <returns></returns>
44
public static bool GetDataBases(ref string [] p_strDataBaseList, string p_strServer, string p_strUser, string p_strPWD)
45![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
46
try
47![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
48
int i = 0;
49
50
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
51
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
52
srv.Connect(p_strServer,p_strUser,p_strPWD);
53
54
if(srv.Databases.Count > 0)
55![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
56
p_strDataBaseList = new string[srv.Databases.Count];
57
58
foreach(SQLDMO.Database db in srv.Databases)
59![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
60
if(db.Name!=null)
61![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
62
p_strDataBaseList[i] = db.Name;
63
}
64
i = i + 1;
65
}
66
}
67
return true;
68
}
69
catch(Exception ex)
70![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
71
throw ex;
72
}
73
}
74
75
#endregion
76
77![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
得到所有的存储过程#region 得到所有的存储过程
78![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
79
/// 得到所有的存储过程
80
/// </summary>
81
/// <param name="p_strProcedureList">存储过程列表</param>
82
/// <param name="p_strServer">服务器名</param>
83
/// <param name="p_strUser">用户名</param>
84
/// <param name="p_strPWD">密码</param>
85
/// <param name="p_strDataBase">数据库名</param>
86
/// <returns></returns>
87
public static bool GetProcedures(ref string [] p_strProcedureList, string p_strServer, string p_strUser, string p_strPWD, string p_strDataBase)
88![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
89
try
90![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
91
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
92
srv.Connect(p_strServer,p_strUser,p_strPWD);
93
94
for(int i=0;i<srv.Databases.Count;i++)
95![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
96
if(srv.Databases.Item(i+1,"dbo").Name == p_strDataBase)
97![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
98
SQLDMO._Database db= srv.Databases.Item(i+1,"dbo");
99
if (db.StoredProcedures.Count > 0)
100![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
101
p_strProcedureList = new string[db.StoredProcedures.Count];
102
103
for(int j=0;j<db.StoredProcedures.Count;j++)
104![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
105
p_strProcedureList[j] = db.StoredProcedures.Item(j+1,"dbo").Name;
106
}
107
break;
108
}
109
}
110
}
111
112
return true;
113
}
114
catch(Exception ex)
115![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
116
throw ex;
117
}
118
}
119
#endregion
120
121![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
得到所有的Tables集合#region 得到所有的Tables集合
122![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
123
/// 得到所有的Tables集合
124
/// </summary>
125
/// <param name="p_strProcedureList">Tables集合</param>
126
/// <param name="p_strServer">服务器名</param>
127
/// <param name="p_strUser">用户名</param>
128
/// <param name="p_strPWD">密码</param>
129
/// <param name="p_strDataBase">数据库名</param>
130
/// <returns></returns>
131
public static bool GetTables(ref string [] p_strTableList, string p_strServer, string p_strUser, string p_strPWD, string p_strDataBase)
132![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
133
try
134![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
135
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
136
srv.Connect(p_strServer,p_strUser,p_strPWD);
137
138
for(int i=0;i<srv.Databases.Count;i++)
139![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
140
if(srv.Databases.Item(i+1,"dbo").Name == p_strDataBase)
141![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
142
SQLDMO._Database db= srv.Databases.Item(i+1,"dbo");
143
if (db.Tables.Count > 0)
144![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
145
p_strTableList = new string[db.Tables.Count];
146
147
for(int j=0;j<db.Tables.Count;j++)
148![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
149
p_strTableList[j] = db.Tables.Item(j+1,"dbo").Name;
150
}
151
break;
152
}
153
}
154
}
155
156
return true;
157
}
158
catch(Exception ex)
159![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
160
throw ex;
161
}
162
}
163
#endregion
164
165![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
得到所有的Views集合#region 得到所有的Views集合
166![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
167
/// 得到所有的Views集合
168
/// </summary>
169
/// <param name="p_strProcedureList">Views集合</param>
170
/// <param name="p_strServer">服务器名</param>
171
/// <param name="p_strUser">用户名</param>
172
/// <param name="p_strPWD">密码</param>
173
/// <param name="p_strDataBase">数据库名</param>
174
/// <returns></returns>
175
public static bool GetViews(ref string [] p_strViewList, string p_strServer, string p_strUser, string p_strPWD, string p_strDataBase)
176![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
177
try
178![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
179
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
180
srv.Connect(p_strServer,p_strUser,p_strPWD);
181
182
for(int i=0;i<srv.Databases.Count;i++)
183![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
184
if(srv.Databases.Item(i+1,"dbo").Name == p_strDataBase)
185![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
186
SQLDMO._Database db= srv.Databases.Item(i+1,"dbo");
187
if (db.Views.Count > 0)
188![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
189
p_strViewList = new string[db.Views.Count];
190
191
for(int j=0;j<db.Views.Count;j++)
192![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
193
p_strViewList[j] = db.Views.Item(j+1,"dbo").Name;
194
}
195
break;
196
}
197
}
198
}
199
200
return true;
201
}
202
catch(Exception ex)
203![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
204
throw ex;
205
}
206
}
207
#endregion