首页 > itarticle > sqlserver常用脚本与基础(待完善)

sqlserver常用脚本与基础(待完善)

admin 11月 13, 2020 0

1 查表名

1
 
SELECT NAME FROM SYSOBJECTS WHERE TYPE='U' order by NAME

2 查所有表行数

1
2
3
4
5
6
7
 
SELECT a.name, b.rows

FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id

WHERE (a.type = 'u') AND (b.indid IN (0, 1))

ORDER BY a.name,b.rows DESC

3 删除所有表数据

1
 
SELECT 'delete from '+NAME+char(13)  FROM SYSOBJECTS WHERE TYPE='U' order by  NAME

4 insert随机数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
SELECT 'insert '+a.NAME+' ('+
STUFF(
		(SELECT ',' + Cast(col as varchar)
		 FROM
		 (
			SELECT syscolumns.name col,systypes.name ctype,syscolumns.isnullable, syscolumns.length  
			FROM syscolumns, systypes  
			WHERE syscolumns.xusertype = systypes.xusertype  AND syscolumns.id = object_id(a.NAME)
			and COLUMNPROPERTY( OBJECT_ID(a.NAME),syscolumns.name,'IsIdentity')<>1
		 ) test
		  
		 
		   FOR XML PATH('')
		 )
		 ,1,1,''
      )+') values('


	 + STUFF(
		(SELECT ',' + Cast(cvalue as varchar)
		 FROM
		 (
			SELECT syscolumns.name col,systypes.name ctype,syscolumns.isnullable, syscolumns.length ,
			(case systypes.name
			 
			 when 'varchar' then 'left(newid(),'+convert(varchar,syscolumns.length)+')'
			 when 'char' then '''a'''
			 when  'int' then 'floor(rand()*100)'
			 when 'datetime' then 'getdate()'
			 when  'money' then 'rand()*10'
			 when  'float' then 'rand()*10'
			 when  'double' then 'rand()*10'
			 else 'floor(rand())'
			 end
			 ) cvalue

			FROM syscolumns, systypes  
			WHERE syscolumns.xusertype = systypes.xusertype  AND syscolumns.id = object_id(a.NAME)
			and COLUMNPROPERTY( OBJECT_ID(a.NAME),syscolumns.name,'IsIdentity')<>1
		 ) test  FOR XML PATH('')
		 )
		 ,1,1,''
      )+')'
	  +char(10)+'go'+
       +char(10)+'select * from '+a.NAME
	    +char(10)+'go'
AS InsertSTR  FROM SYSOBJECTS a WHERE TYPE='U' order by a.NAME

powerdesigner导实体的excel数据字典脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
 
'******************************************************************************

'* File:     pdm2excel.txt

'* Title:    pdm export to excel

'* Purpose:  To export the tables and columns to Excel

'* Model:    Physical Data Model

'* Objects:  Table, Column, View

'* Author:   ziyan

'* Created:  2012-05-03

'* Version:  1.0

'******************************************************************************

Option Explicit

   Dim rowsNum

   rowsNum = 0

'-----------------------------------------------------------------------------

' Main function

'-----------------------------------------------------------------------------

' Get the current active model

Dim Model

Set Model = ActiveModel

If (Model Is Nothing) Or (Not Model.IsKindOf(PdPDM.cls_Model)) Then

  MsgBox "The current model is not an PDM model."

Else

 ' Get the tables collection

 '创建EXCEL APP

 dim beginrow

 DIM EXCEL, SHEET

 set EXCEL = CREATEOBJECT("Excel.Application")

 EXCEL.workbooks.add(-4167)'添加工作表


 EXCEL.workbooks(1).sheets(1).name ="数据字典"
 

 set sheet = EXCEL.workbooks(1).sheets("数据字典")
 
 

 ShowProperties Model, SHEET

 EXCEL.visible = true

 '设置列宽和自动换行

 sheet.Columns(1).ColumnWidth = 10 

 sheet.Columns(2).ColumnWidth = 20 

 sheet.Columns(3).ColumnWidth = 20 

 sheet.Columns(4).ColumnWidth = 20 

 sheet.Columns(5).ColumnWidth = 40 

 sheet.Columns(2).WrapText =true

 sheet.Columns(3).WrapText =true

 sheet.Columns(5).WrapText =true

 End If

'-----------------------------------------------------------------------------

' Show properties of tables

'-----------------------------------------------------------------------------

Sub ShowProperties(mdl, sheet)

   ' Show tables of the current model/package

   rowsNum=0

   beginrow = rowsNum+1

   ' For each table

   output "begin"

   Dim tab

   For Each tab In mdl.tables

      ShowTable tab,sheet

   Next

   if mdl.tables.count > 0 then

        sheet.Range("A" & beginrow + 1 & ":A" & rowsNum).Rows.Group

   end if

   output "end"

End Sub

'-----------------------------------------------------------------------------

' Show table properties

'-----------------------------------------------------------------------------

Sub ShowTable(tab, sheet)

   If IsObject(tab) Then

     Dim rangFlag

     rowsNum = rowsNum + 1

      ' Show properties

      Output "================================"

      sheet.cells(rowsNum, 1) = ""

      sheet.cells(rowsNum, 2) = "表名"

      sheet.cells(rowsNum, 3) = tab.code

      sheet.cells(rowsNum, 5) = tab.name

      sheet.Range(sheet.cells(rowsNum, 3),sheet.cells(rowsNum, 4)).Merge

      rowsNum = rowsNum + 1


      sheet.cells(rowsNum, 5) = "说明"

      sheet.cells(rowsNum, 1) = ""

      sheet.cells(rowsNum, 2) = "字段中文名"

      sheet.cells(rowsNum, 3) = "字段名"

      sheet.cells(rowsNum, 4) = "字段类型"

      '设置边框

      sheet.Range(sheet.cells(rowsNum-1, 2),sheet.cells(rowsNum, 5)).Borders.LineStyle = "1"
      sheet.Range(sheet.cells(rowsNum-1, 2),sheet.cells(rowsNum, 5)).Interior.Color = RGB(100,200,200)
       

Dim col ' running column

Dim colsNum

colsNum = 0

      for each col in tab.columns

        rowsNum = rowsNum + 1

        colsNum = colsNum + 1

      

      sheet.cells(rowsNum, 5) = col.comment

        sheet.cells(rowsNum, 1) = ""

      sheet.cells(rowsNum, 2) = col.name

      sheet.cells(rowsNum, 3) = col.code

      sheet.cells(rowsNum, 4) = col.datatype

      next

      sheet.Range(sheet.cells(rowsNum-colsNum+1,2),sheet.cells(rowsNum,5)).Borders.LineStyle = "1"       

      

      rowsNum = rowsNum + 1

      

      Output "FullDescription: "       + tab.Name

   End If

End Sub
微海报分享

近期文章

近期评论

标签

热门

文章归档

分类目录

功能