收藏几段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
GO
SET NOCOUNT ON
GO
SET LANGUAGE 'Simplified Chinese'
go
DECLARE @tbl nvarchar(200),@fld nvarchar(200),@sql nvarchar(4000),@maxlen int,@sample nvarchar(40)
SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t
FROM syscolumns a, systypes b,sysobjects d
WHERE a.xtype=b.xusertype and a.id=d.id and d.xtype='U'
DECLARE read_cursor CURSOR
FOR SELECT TableName,FieldName FROM #t
SELECT TOP 1 '_TableName ' TableName,
'FieldName ' FieldName,'TypeName ' TypeName,
'Length' Length,'IS_NULL' IS_NULL,
'MaxLenUsed' AS MaxLenUsed,'Sample Value ' Sample,
'Comment ' Comment INTO #tc FROM #t
OPEN read_cursor
FETCH NEXT FROM read_cursor INTO @tbl,@fld
WHILE (@@fetch_status <> -1) --- failes
BEGIN
IF (@@fetch_status <> -2) -- Missing
BEGIN
SET @sql=N'SET @maxlen=(SELECT max(len(cast('+@fld+' as nvarchar))) FROM '+@tbl+')'
--PRINT @sql
EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT
--print @maxlen
SET @sql=N'SET @sample=(SELECT TOP 1 cast('+@fld+' as nvarchar) FROM '+@tbl+' WHERE len(cast('+@fld+' as nvarchar))='+convert(nvarchar(5),@maxlen)+')'
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
--for quickly
--SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+
--@tbl+' order by 1 desc ))'
PRINT @sql
print @sample
print @tbl
EXEC SP_EXECUTESQL @sql,N'@sample nvarchar(30) OUTPUT',@sample OUTPUT
INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,
convert(nchar(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld
END
FETCH NEXT FROM read_cursor INTO @tbl,@fld
END
CLOSE read_cursor
DEALLOCATE read_cursor
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO
select count(*) from #t
DROP TABLE #t
GO
select count(*)-1 from #tc
select * into ##tx from #tc order by tablename
DROP TABLE #tc
--select * from ##tx
declare @db nvarchar(60),@sql nvarchar(3000)
set @db=db_name()
--请修改用户名和口令 导出到Excel 中
set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:\'+@db+'_exp.xls -w -C936 -Usa -Psa '''
print @sql
exec(@sql)
GO
DROP TABLE ##tx
GO
-- ======================================================
--根据表中数据生成insert语句的存储过程
--建立存储过程,执行 spGenInsertSQL 表名
--感谢playyuer
-- ======================================================
CREATE proc spGenInsertSQL (@tablename varchar(256))
as
begin
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql =' ('
set @sqlValues = 'values (''+'
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
from
(select case
when xtype in (48,52,56,59,60,62,104,106,108,122,127)
then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
when xtype in (58,61)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
when xtype in (167)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (231)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (175)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
when xtype in (239)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
else '''NULL'''
end as Cols,name
from syscolumns
where id = object_id(@tablename)
) T
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
--print @sql
exec (@sql)
end
GO
-- ======================================================
--根据表中数据生成insert语句的存储过程
--建立存储过程,执行 proc_insert 表名
--感谢Sky_blue
-- ======================================================
CREATE proc proc_insert (@tablename varchar(256))
as
begin
set nocount on
declare @sqlstr varchar(4000)
declare @sqlstr1 varchar(4000)
declare @sqlstr2 varchar(4000)
select @sqlstr='select ''insert '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' values ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
-- 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'
when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
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'
when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
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'
when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
-- 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'
when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
else '''NULL'''
end as col,a.colid,a.name
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
)t order by colid
select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
-- print @sqlstr
exec( @sqlstr)
set nocount off
end
GO
分享到:
相关推荐
SQL Server中存储过程比直接运行SQL语句慢的原因
功能: 根据表名、where条件,生成导出数据的SQL语句。(包含insert语句。结果可一键执行,利于数据导出、导入) 参数: @tableName nvarchar(100) --表名 ,@sqlWhere nvarchar(500) --where条件(传空时,导出全部...
oracle到sqlserver 存储过程语法转换,希望对大家有帮助啊! 最近刚把oracle的数据库移植到sqlserver!
数据库中的if和else语句 14 SQL中的while语句 14 Case-End多分支语句 14 子查询 15 视图(虚拟表)和索引 16 视图 16 索引 16 事务管理 17 数据库的安全性 18 建立权限 19 存储过程(procedure) 20 无参数...
资源名称:SQL_Server存储过程调试指南内容简介: 存储过程( Stored Procedure)是一组为了完成特定功能的 SQL 语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来...
可以支持导出单表的建表语句,也可以支持单库所有建表语句的导出以及同服务器跨库建表语句的导出。
SQLServer动态SQL语句的用法
2.利用SQL Server配置管理器启动、停止SQL Server服务(包括默认实例和命名实例),配置SQL Server服务为自动启动。 3.利用SQL Server配置管理器配置进行SQL Server 2005网络配置,启用默认实例和命名实例的TCP/IP...
学习存储过称基本技巧,讲述SQL SERVER语句与存储过程基本联系与技巧
此工具可以将SQL Server表数据导出成Insert语句。SQL Server的导入导出功能可以导出创建数据库各对象的脚本,却不提供导出目标表的现有数据为Insert语句的功能,此工具可以将SQL Server表数据导出成Insert语句。
针对sqlserver 2008 存储过程通过With Encryption加密方式的解密
sql server数据库中分页的存储过程,参数中比较灵活,如果首页传1,非首页传其他。
SQL Server 存储过程及Oracle SQL语句分页
1.1 核心代码: 1.2 直接执行 Sql 语句 1.3 不带参数的存储过程 1.4 带参数的存储过程 2.1 核心代码: 2.2 直接执行 sql 语句 2
SQL Server 数据库管理常用的SQL和T-SQL语句 SQL SERVER 与ACCESS、EXCEL的数据转换 sql server中,日期比较、日期查询的...根据表中数据生成insert语句的存储过程.txt 精妙的SQL语句.txt 事务处理.txt php_ADODB.txt
–我的电脑–控制面板–管理工具–服务–右键SQLSERVERAGENT–属性–启动类型–选择”自动启动”–确定. 2、SQL Server Management Studio — SQL Server 代理 — 作业 — 新建作业 作业设置如下: “常规” — 设置...
存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。 存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回...
sqlserver 分页调用的存储过程 t-sql 语句
SQLSERVER存储过程使用说明书引言首先介绍一下什么是存储过程:存储过程就是将常用的或很复杂的工作,预先用SQL语句写好并用一个指定的名称存储起来,并且这样的语句是放在数据库中的,还可以根据条件执行不同SQL...