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

关注

原创最新推荐文章于 2025-12-20 09:23:27 发布 2.2k 阅读 AI 写同款· GEO 优化 ›

SQL Server存储过程中执行动态SQL语句一、简介在Sql Server中执行动态sql语句有两种方法：exec和exec sp_executesql。优缺点： 1、exec sp_executesql提供输入、输出接口，可以在sql语句中进行参数占位；exec则不可以。 2、exec sp_executesql能够重用执行计划，提高了执行效率；exec则不可以。 3、exec sp_executesql更安阅读详情

-- ======================================================
2

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

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

-- ======================================================
8

Select
10

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

A.Colorder 字段序号,
14

A.Name 字段名,
16

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

(Case When (Select Count(*)
20

From Sysobjects
22

Where (Name In
24

(Select Name
26

From Sysindexes
28

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

(Select Indid
32

From Sysindexkeys
34

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

(Select Colid
38

From Syscolumns
40

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

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

B.Name 类型,
46

A.Length 占用字节数,
48

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

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

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

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

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

From Syscolumns A Left Join Systypes B
60

On A.Xtype=B.Xusertype
62

Inner Join Sysobjects D
64

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

Left Join Syscomments E
68

On A.Cdefault=E.Id
70

Left Join Sysproperties G
72

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

Order By A.Id,A.Colorder
76

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

并导出到Excel 中
80

-- ======================================================
82

-- Export all user tables definition and one sample value
84

-- jan-13-2003,Dr.Zhang
86

-- ======================================================
88

在查询分析器里运行：
90

SET ANSI_NULLS OFF
92

GO
94

SET NOCOUNT ON
96

GO
98

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