1

--

 ======================================================

  2

  3

--

列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息

  4

  5

--

在查询分析器里运行即可,可以生成一个表，导出到EXCEL中

  6

  7

--

 ======================================================

  8

  9

Select

 

 10

 11

       (

Case

 

When

 A.Colorder

=

1

 

Then

 D.Name 

Else

 

''

 

End

)表名,

 12

 13

       A.Colorder 字段序号,

 14

 15

       A.Name 字段名,

 16

 17

       (

Case

 

When

 

Columnproperty

( A.Id,A.Name,

'

Isidentity

'

)

=

1

 

Then

 

'

√

'

Else

 

''

 

End

) 标识,

 18

 19

       (

Case

 

When

 (

Select

 

Count

(

*

)

 20

 21

       

From

 Sysobjects

 22

 23

       

Where

 (Name 

In

 24

 25

                 (

Select

 Name

 26

 27

                

From

 Sysindexes

 28

 29

                

Where

 (Id 

=

 A.Id) 

And

 (Indid 

In

 30

 31

                          (

Select

 Indid

 32

 33

                         

From

 Sysindexkeys

 34

 35

                         

Where

 (Id 

=

 A.Id) 

And

 (Colid 

In

 36

 37

                                   (

Select

 Colid

 38

 39

                                  

From

 Syscolumns

 40

 41

                                  

Where

 (Id 

=

 A.Id) 

And

 (Name 

=

 A.Name))))))) 

And

 42

 43

              (Xtype 

=

 

'

Pk

'

))

>

0

 

Then

 

'

√

'

 

Else

 

''

 

End

) 主键,

 44

 45

       B.Name 类型,

 46

 47

       A.Length 占用字节数,

 48

 49

       

Columnproperty

(A.Id,A.Name,

'

Precision

'

) 

As

 长度,

 50

 51

       

Isnull

(

Columnproperty

(A.Id,A.Name,

'

Scale

'

),

0

) 

As

 小数位数,

 52

 53

       (

Case

 

When

 A.Isnullable

=

1

 

Then

 

'

√

'

Else

 

''

 

End

) 允许空,

 54

 55

       

Isnull

(E.

Text

,

''

) 默认值,

 56

 57

       

Isnull

(G.

[

Value

]

,

''

) 

As

 字段说明    

 58

 59

From

  Syscolumns  A 

Left

 

Join

 Systypes B 

 60

 61

On

  A.Xtype

=

B.Xusertype

 62

 63

Inner

 

Join

 Sysobjects D 

 64

 65

On

 A.Id

=

D.Id  

And

  D.Xtype

=

'

U

'

 

And

  D.Name

<>

'

Dtproperties

'

 66

 67

Left

 

Join

 Syscomments E

 68

 69

On

 A.Cdefault

=

E.Id

 70

 71

Left

 

Join

 Sysproperties G

 72

 73

On

 A.Id

=

G.Id 

And

 A.Colid 

=

 G.Smallid  

 74

 75

Order

 

By

 A.Id,A.Colorder

 76

 77

列出SQL SERVER 所有表、字段定义，类型，长度，一个值等信息

 78

 79

并导出到Excel 中

 80

 81

--

 ======================================================

 82

 83

--

 Export all user tables definition and one sample value

 84

 85

--

 jan-13-2003,Dr.Zhang

 86

 87

--

 ======================================================

 88

 89

在查询分析器里运行：

 90

 91

SET

 ANSI_NULLS 

OFF

 

 92

 93

GO

 94

 95

SET

 NOCOUNT 

ON

 96

 97

GO

 98

 99

 

100

101

SET

 LANGUAGE 

'

Simplified Chinese

'

102

103

go

104

105

DECLARE

 

@tbl

 

nvarchar

(

200

),

@fld

 

nvarchar

(

200

),

@sql

 

nvarchar

(

4000

),

@maxlen

 

int

,

@sample

 

nvarchar

(

40

)

106

107

 

108

109

SELECT

 d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL 

INTO

 #t

110

111

FROM

  syscolumns  a,  systypes b,sysobjects d  

112

113

WHERE

  a.xtype

=

b.xusertype  

and

  a.id

=

d.id  

and

  d.xtype

=

'

U

'

 

114

115

 

116

117

DECLARE

 read_cursor 

CURSOR

118

119

FOR

 

SELECT

 TableName,FieldName 

FROM

 #t

120

121

 

122

123

SELECT

 

TOP

 

1

 

'

_TableName                     

'

 TableName,

124

125

            

'

FieldName                      

'

 FieldName,

'

TypeName             

'

 TypeName,

126

127

            

'

Length

'

 Length,

'

IS_NULL

'

 IS_NULL, 

128

129

            

'

MaxLenUsed

'

 

AS

 MaxLenUsed,

'

Sample Value          

'

 Sample,

130

131

             

'

Comment   

'

 Comment 

INTO

 #tc 

FROM

 #t

132

133

 

134

135

OPEN

 read_cursor

136

137

 

138

139

FETCH

 

NEXT

 

FROM

 read_cursor 

INTO

 

@tbl

,

@fld

140

141

WHILE

 (

@@fetch_status

 

<>

 

-

1

)  

--

- failes

142

143

BEGIN

144

145

       

IF

 (

@@fetch_status

 

<>

 

-

2

) 

--

 Missing

146

147

       

BEGIN

148

149

              

SET

 

@sql

=

N

'

SET @maxlen=(SELECT max(len(cast(

'

+

@fld

+

'

 as nvarchar))) FROM 

'

+

@tbl

+

'

)

'

150

151

              

--

PRINT @sql

152

153

              

EXEC

 SP_EXECUTESQL 

@sql

,N

'

@maxlen int OUTPUT

'

,

@maxlen

 OUTPUT

154

155

              

--

print @maxlen

156

157

              

SET

 

@sql

=

N

'

SET @sample=(SELECT TOP 1 cast(

'

+

@fld

+

'

 as nvarchar) FROM 

'

+

@tbl

+

'

 WHERE len(cast(

'

+

@fld

+

'

 as nvarchar))=

'

+

convert

(

nvarchar

(

5

),

@maxlen

)

+

'

)

'

158

159

              

EXEC

 SP_EXECUTESQL 

@sql

,N

'

@sample varchar(30) OUTPUT

'

,

@sample

 OUTPUT

160

161

              

--

for quickly   

162

163

              

--

SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+

164

165

                     

--

@tbl+' order by 1 desc ))'  

166

167

              

PRINT

 

@sql

168

169

              

print

 

@sample

170

171

              

print

 

@tbl

172

173

              

EXEC

 SP_EXECUTESQL 

@sql

,N

'

@sample nvarchar(30) OUTPUT

'

,

@sample

 OUTPUT

174

175

              

INSERT

 

INTO

 #tc 

SELECT

 

*

,

ltrim

(

ISNULL

(

@maxlen

,

0

)) 

as

 MaxLenUsed,

176

177

                     

convert

(

nchar

(

20

),

ltrim

(

ISNULL

(

@sample

,

'

 

'

))) 

as

 Sample,

'

 

'

 Comment 

FROM

 #t 

where

 TableName

=

@tbl

 

and

 FieldName

=

@fld

178

179

       

END

180

181

       

FETCH

 

NEXT

 

FROM

 read_cursor 

INTO

 

@tbl

,

@fld

182

183

END

184

185

 

186

187

CLOSE

 read_cursor

188

189

DEALLOCATE

 read_cursor

190

191

GO

192

193

 

194

195

SET

 ANSI_NULLS 

ON

196

197

GO

198

199

SET

 NOCOUNT 

OFF

200

201

GO

202

203

select

 

count

(

*

)  

from

 #t

204

205

DROP

 

TABLE

 #t

206

207

GO

208

209

 

210

211

select

 

count

(

*

)

-

1

  

from

 #tc

212

213

 

214

215

select

 

*

 

into

 ##tx 

from

 #tc 

order

 

by

 tablename

216

217

DROP

 

TABLE

 #tc

218

219

 

220

221

--

select * from ##tx

222

223

 

224

225

declare

 

@db

 

nvarchar

(

60

),

@sql

 

nvarchar

(

3000

)

226

227

set

 

@db

=

db_name

()

228

229

--

请修改用户名和口令 导出到Excel 中

230

231

set

 

@sql

=

'

exec master.dbo.xp_cmdshell 

''

bcp ..dbo.##tx out c:\

'

+

@db

+

'

_exp.xls -w -C936 -Usa -Psa 

'''

232

233

print

 

@sql

234

235

exec

(

@sql

)

236

237

GO

238

239

DROP

 

TABLE

 ##tx

240

241

GO

242

243

 

244

245

 

246

247

 

248

249

--

 ======================================================

250

251

--

根据表中数据生成insert语句的存储过程

252

253

--

建立存储过程，执行 spGenInsertSQL 表名

254

255

--

感谢playyuer

256

257

--

 ======================================================

258

259

CREATE

   

proc

 spGenInsertSQL (

@tablename

 

varchar

(

256

))

260

261

 

262

263

as

264

265

begin

266

267

  

declare

 

@sql

 

varchar

(

8000

)

268

269

  

declare

 

@sqlValues

 

varchar

(

8000

)

270

271

  

set

 

@sql

 

=

'

 (

'

272

273

  

set

 

@sqlValues

 

=

 

'

values (

''

+

'

274

275

  

select

 

@sqlValues

 

=

 

@sqlValues

 

+

 cols 

+

 

'

 + 

''

,

''

 + 

'

 ,

@sql

 

=

 

@sql

 

+

 

'

[

'

 

+

 name 

+

 

'

],

'

 

276

277

    

from

 

278

279

        (

select

 

case

 

280

281

                  

when

 xtype 

in

 (

48

,

52

,

56

,

59

,

60

,

62

,

104

,

106

,

108

,

122

,

127

)                                

282

283

                       

then

 

'

case when 

'

+

 name 

+

'

 is null then 

''

NULL

''

 else 

'

 

+

 

'

cast(

'

+

 name 

+

 

'

 as varchar)

'

+

'

 end

'

284

285

                  

when

 xtype 

in

 (

58

,

61

)

286

287

                       

then

 

'

case when 

'

+

 name 

+

'

 is null then 

''

NULL

''

 else 

'

+

'''''''''

 + 

'

 

+

 

'

cast(

'

+

 name 

+

'

 as varchar)

'

+

 

'

+

'''''''''

+

'

 end

'

288

289

                 

when

 xtype 

in

 (

167

)

290

291

                       

then

 

'

case when 

'

+

 name 

+

'

 is null then 

''

NULL

''

 else 

'

+

'''''''''

 + 

'

 

+

 

'

replace(

'

+

 name

+

'

,

''''''''

,

''''''''''''

)

'

 

+

 

'

+

'''''''''

+

'

 end

'

292

293

                  

when

 xtype 

in

 (

231

)

294

295

                       

then

 

'

case when 

'

+

 name 

+

'

 is null then 

''

NULL

''

 else 

'

+

'''

N

''''''

 + 

'

 

+

 

'

replace(

'

+

 name

+

'

,

''''''''

,

''''''''''''

)

'

 

+

 

'

+

'''''''''

+

'

 end

'

296

297

                  

when

 xtype 

in

 (

175

)

298

299

                       

then

 

'

case when 

'

+

 name 

+

'

 is null then 

''

NULL

''

 else 

'

+

'''''''''

 + 

'

 

+

 

'

cast(replace(

'

+

 name

+

'

,

''''''''

,

''''''''''''

) as Char(

'

 

+

 

cast

(length 

as

 

varchar

)  

+

 

'

))+

'''''''''

+

'

 end

'

300

301

                  

when

 xtype 

in

 (

239

)

302

303

                       

then

 

'

case when 

'

+

 name 

+

'

 is null then 

''

NULL

''

 else 

'

+

'''

N

''''''

 + 

'

 

+

 

'

cast(replace(

'

+

 name

+

'

,

''''''''

,

''''''''''''

) as Char(

'

 

+

 

cast

(length 

as

 

varchar

)  

+

 

'

))+

'''''''''

+

'

 end

'

304

305

                  

else

 

'''

NULL

'''

306

307

                

end

 

as

 Cols,name

308

309

           

from

 syscolumns  

310

311

          

where

 id 

=

 

object_id

(

@tablename

) 

312

313

        ) T 

314

315

  

set

 

@sql

 

=

'

select 

''

INSERT INTO [

'

+

 

@tablename

 

+

 

'

]

'

 

+

 

left

(

@sql

,

len

(

@sql

)

-

1

)

+

'

) 

'

 

+

 

left

(

@sqlValues

,

len

(

@sqlValues

)

-

4

) 

+

 

'

)

''

 from 

'

+

@tablename

316

317

  

--

print @sql

318

319

  

exec

 (

@sql

)

320

321

end

322

323

 

324

325

GO

326

327

 

328

329

 

330

331

 

332

333

--

 ======================================================

334

335

--

根据表中数据生成insert语句的存储过程

336

337

--

建立存储过程，执行 proc_insert 表名

338

339

--

感谢Sky_blue

340

341

--

 ======================================================

342

343

 

344

345

CREATE

 

proc

 proc_insert (

@tablename

 

varchar

(

256

))

346

347

as

348

349

begin

350

351

       

set

 nocount 

on

352

353

       

declare

 

@sqlstr

 

varchar

(

4000

)

354

355

       

declare

 

@sqlstr1

 

varchar

(

4000

)

356

357

       

declare

 

@sqlstr2

 

varchar

(

4000

)

358

359

       

select

 

@sqlstr

=

'

select 

''

insert 

'

+

@tablename

360

361

       

select

 

@sqlstr1

=

''

362

363

       

select

 

@sqlstr2

=

'

 (

'

364

365

       

select

 

@sqlstr1

=

 

'

 values ( 

''

+

'

366

367

       

select

 

@sqlstr1

=

@sqlstr1

+

col

+

'

+

''

,

''

+

'

 ,

@sqlstr2

=

@sqlstr2

+

name 

+

'

,

'

 

from

 (

select

 

case

 

368

369

--

     when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'

370

371

       

when

 a.xtype 

=

104

 

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'

convert(varchar(1),

'

+

a.name 

+

'

)

'

+

'

 end

'

372

373

       

when

 a.xtype 

=

175

 

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'''''''''

+

'

+

'

replace(

'

+

a.name

+

'

,

''''''''

,

''''''''''''

)

'

 

+

 

'

+

'''''''''

+

'

 end

'

374

375

       

when

 a.xtype 

=

61

  

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'''''''''

+

'

+

'

convert(varchar(23),

'

+

a.name 

+

'

,121)

'

+

 

'

+

'''''''''

+

'

 end

'

376

377

       

when

 a.xtype 

=

106

 

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'

convert(varchar(

'

+

convert

(

varchar

(

4

),a.xprec

+

2

)

+

'

),

'

+

a.name 

+

'

)

'

+

'

 end

'

378

379

       

when

 a.xtype 

=

62

  

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'

convert(varchar(23),

'

+

a.name 

+

'

,2)

'

+

'

 end

'

380

381

       

when

 a.xtype 

=

56

  

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'

convert(varchar(11),

'

+

a.name 

+

'

)

'

+

'

 end

'

382

383

       

when

 a.xtype 

=

60

  

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'

convert(varchar(22),

'

+

a.name 

+

'

)

'

+

'

 end

'

384

385

       

when

 a.xtype 

=

239

 

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'''''''''

+

'

+

'

replace(

'

+

a.name

+

'

,

''''''''

,

''''''''''''

)

'

 

+

 

'

+

'''''''''

+

'

 end

'

386

387

       

when

 a.xtype 

=

108

 

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'

convert(varchar(

'

+

convert

(

varchar

(

4

),a.xprec

+

2

)

+

'

),

'

+

a.name 

+

'

)

'

+

'

 end

'

388

389

       

when

 a.xtype 

=

231

 

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'''''''''

+

'

+

'

replace(

'

+

a.name

+

'

,

''''''''

,

''''''''''''

)

'

 

+

 

'

+

'''''''''

+

'

 end

'

390

391

       

when

 a.xtype 

=

59

  

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'

convert(varchar(23),

'

+

a.name 

+

'

,2)

'

+

'

 end

'

392

393

       

when

 a.xtype 

=

58

  

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'''''''''

+

'

+

'

convert(varchar(23),

'

+

a.name 

+

'

,121)

'

+

 

'

+

'''''''''

+

'

 end

'

394

395

       

when

 a.xtype 

=

52

  

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'

convert(varchar(12),

'

+

a.name 

+

'

)

'

+

'

 end

'

396

397

       

when

 a.xtype 

=

122

 

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'

convert(varchar(22),

'

+

a.name 

+

'

)

'

+

'

 end

'

398

399

       

when

 a.xtype 

=

48

  

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'

convert(varchar(6),

'

+

a.name 

+

'

)

'

+

'

 end

'

400

401

--

     when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'

402

403

       

when

 a.xtype 

=

167

 

then

 

'

case when 

'

+

a.name

+

'

 is null then 

''

NULL

''

 else 

'

+

'''''''''

+

'

+

'

replace(

'

+

a.name

+

'

,

''''''''

,

''''''''''''

)

'

 

+

 

'

+

'''''''''

+

'

 end

'

404

405

       

else

 

'''

NULL

'''

406

407

       

end

 

as

 col,a.colid,a.name

408

409

       

from

 syscolumns a 

where

 a.id 

=

 

object_id

(

@tablename

) 

and

 a.xtype 

<>

189

 

and

 a.xtype 

<>

34

 

and

 a.xtype 

<>

35

 

and

  a.xtype 

<>

36

410

411

       )t 

order

 

by

 colid

412

413

       

414

415

       

select

 

@sqlstr

=

@sqlstr

+

left

(

@sqlstr2

,

len

(

@sqlstr2

)

-

1

)

+

'

) 

'

+

left

(

@sqlstr1

,

len

(

@sqlstr1

)

-

3

)

+

'

)

''

 from 

'

+

@tablename

416

417

--

  print @sqlstr

418

419

       

exec

( 

@sqlstr

)

420

421

       

set

 nocount 

off

422

423

end

424

425

GO