| 标题 | SQL获取表结构的show_table.vbs (冰点极限NP) |
| 内容 | SQL获取表结构的show_table.vbs，用vbs实现查看sql数据库的表结构的代码。需要传入用户名密码与数据库名称，具体的看程序帮助。 代码如下: set arg=wscript.arguments If arg.count = 0 Then show_help() wsh.quit End If Server = arg(0) User = arg(1) pass = arg(2) database_name = arg(3) table_name = arg(4) set Conn = CreateObject("ADODB.Connection") Conn.Open "Driver={SQL Server};Server=" & Server & ";UID=" & User & ";PWD=" & Pass & ";Database=" & database_name Set rs = conn.execute("select count(*) as n from " & database_name & ".dbo.sysobjects where id = object_id(N'[dbo].[" & table_name & "]')") if rs("n") <=0 Then wsh.echo "table have???" wsh.quit End If sql1="use " &database_name & ";select count(column_name) from information_schema.columns where table_name='" & table_name & "'" set rs1=conn.execute(sql1) num = rs1(0) rs1.close Set rs1 = Nothing i = 1 sql2="use " &database_name & ";select column_name,data_type,IS_NULLABLE,character_octet_length,collation_name,domain_name from information_schema.columns where table_name='" & table_name & "' order by ordinal_position" 'wsh.echo sql2 set rs2=conn.execute (sql2) wsh.echo "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" & table_name & "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)" wsh.echo "drop table [dbo].[" & table_name & "]" wsh.echo "GO" wsh.echo "CREATE TABLE [dbo].[" & table_name & "] (" do while not rs2.eof col_dn = rs2("domain_name") col_name = rs2("column_name") col_type = rs2("data_type") col_len = rs2("character_octet_length") col_an = rs2("collation_name") col_is = rs2("IS_NULLABLE") sql = chr(9)&"[" & col_name & "] " If col_dn = "id" Then sql = sql & col_dn &" " Else sql = sql & "[" & col_type & "] " If col_len <> "" Then sql = sql & "(" & col_len & ") " End If If col_an <> "" Then sql = sql & "COLLATE " & col_an End If End If If col_is = "No" Then sql = sql & " NOT NULL " Else sql = sql & " NULL " End If If i = num Then sql = sql & Chr(13)&Chr(10)&") ON [PRIMARY]" Else sql = sql & "," End If i = i +1 wsh.echo sql rs2.movenext loop wsh.echo "GO" rs2.close Set rs2 = Nothing conn.close Set conn=Nothing Sub show_help() wsh.echo "code by N37P47ch " wsh.echo "cscript show_table.vbs ip user pass database table" End Sub |
| 随便看 |
|
在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。