一些有用的SQL Server语句和存储过程

-- ======================================================

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

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

-- ======================================================

Select

(Case When A.Colorder=1 Then D.Name Else '' End)表名,

A.Colorder 字段序号,

A.Name 字段名,

(Case When Columnproperty( A.Id,A.Name,'Isidentity')=1 Then '√'Else '' End) 标识,

(Case When (Select Count(*)

From Sysobjects

Where (Name In

(Select Name

From Sysindexes

Where (Id = A.Id) And (Indid In

(Select Indid

From Sysindexkeys

Where (Id = A.Id) And (Colid In

(Select Colid

From Syscolumns

Where (Id = A.Id) And (Name = A.Name))))))) And

(Xtype = 'Pk'))>0 Then '√' Else '' End) 主键,

B.Name 类型,

A.Length 占用字节数,

Columnproperty(A.Id,A.Name,'Precision') As 长度,

Isnull(Columnproperty(A.Id,A.Name,'Scale'),0) As 小数位数,

(Case When A.Isnullable=1 Then '√'Else '' End) 允许空,

Isnull(E.Text,'') 默认值,

Isnull(G.[Value],'') As 字段说明

From Syscolumns A Left Join Systypes B

On A.Xtype=B.Xusertype

Inner Join Sysobjects D

On A.Id=D.Id And D.Xtype='U' And D.Name<>'Dtproperties'

Left Join Syscomments E

On A.Cdefault=E.Id

Left Join Sysproperties G

On A.Id=G.Id And A.Colid = G.Smallid

Order By A.Id,A.Colorder

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

并导出到Excel 中

-- ======================================================

-- Export all user tables definition and one sample value

-- jan-13-2003,Dr.Zhang

-- ======================================================

在查询分析器里运行：

SET ANSI_NULLS OFF

SET NOCOUNT ON

100

101

SET LANGUAGE 'Simplified Chinese'

102

103

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

192

193

194

195

SET ANSI_NULLS ON

196

197

198

199

SET NOCOUNT OFF

200

201

202

203

select count(*) from #t

204

205

DROP TABLE #t

206

207

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

238

239

DROP TABLE ##tx

240

241

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

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

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

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