sqlserver获取数据库表结构的存储过程（sqlserver获取数据库名）

25-03-14 8

此处将为大家介绍关于sqlserver获取数据库表结构的存储过程的详细内容，并且为您解答有关sqlserver获取数据库名的相关问题，此外，我们还将为您介绍关于MSSQLServer基础07（事务，存

此处将为大家介绍关于sqlserver获取数据库表结构的存储过程的详细内容，并且为您解答有关sqlserver获取数据库名的相关问题，此外，我们还将为您介绍关于MSSQLServer基础07（事务，存储过程，分页的存储过程,触发器）、SQL Server sql处理数据库锁的存储过程分享、Sql Server 导出数据库表结构的SQL查询语句、Sql Server 数据库表结构，存储过程，视图比较脚本的有用信息。

本文目录一览：

sqlserver获取数据库表结构的存储过程（sqlserver获取数据库名）
MSSQLServer基础07（事务，存储过程，分页的存储过程,触发器）
SQL Server sql处理数据库锁的存储过程分享
Sql Server 导出数据库表结构的SQL查询语句
Sql Server 数据库表结构，存储过程，视图比较脚本

sqlserver获取数据库表结构的存储过程（sqlserver获取数据库名）

create PROCEDURE [dbo].[GetTableExplain]
    @TABLENAME varchar(100)
AS
SELECT
     表名       = Case When A.colorder=1 Then D.name Else '--'+D.name End,
     表说明     = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,
     字段序号   = A.colorder,
     字段名     = A.name,
     字段说明   = isnull(G.[value],''),
     标识       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,
     主键       = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (
                      SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,
     类型       = B.name,
     占用字节数 = A.Length,
     长度       = COLUMNPROPERTY(A.id,'PRECISION'),
     小数位数   = isnull(COLUMNPROPERTY(A.id,'Scale'),0),
     允许空     = Case When A.isnullable=1 Then '√'Else '' End,
     默认值     = isnull(E.Text,'')
FROM
     syscolumns A
Left Join
     systypes B
On
     A.xusertype=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
sys.extended_properties G
on
     A.id=G.major_id and A.colid=G.minor_id
Left Join

sys.extended_properties F
On
D.id=F.major_id and F.minor_id=0
where d.name=@TABLENAME --如果只查询指定表,加上此条件 Order By A.id,A.colorder

MSSQLServer基础07（事务，存储过程，分页的存储过程,触发器）

事务

事务：保证多个操作全部成功，否则全部失败，这处机制就是事务
思考：下了个订单，但是在保存详细信息时出错了，这样可以成功吗？
数据库中的事务：代码全都成功则提交，如果有某一条语句失败则回滚，整体失败
事务操作：
begin transaction--开始事务
comit transaction--提交，没错后执行
rollback transaction--回滚，出错后执行，执行了的操作会回滚，不会生效
用法：声明一个变量，记录错误信息，最终根据变量值是否大于0，进行提交或回滚

示例：模拟转账操作：一个账户扣钱，一个账户加钱

begin transaction
declare @sumError int=0
update bank set balance=balance-1000 where cid='0001'
set @sumError=@sumError+@@Error
update bank set balance=balance+1000 where cid='0002'
set @sumError=@sumError+@@Error
if(@sumError)
begin
---失败了
rollback transaction
end
else
begin
---成功了
comit transaction
end

存储过程

就是一个函数，用于存储一段处理代码
好处：
完成代码的封装，实现代码重用；
安全
方便应用程序与数据库间通信，不用传大量sql语句过程，而只用传一个存储过程名称过来，简单方便
系统存储过程（在界面中打开看一看）
自定义存储过程
create proc usp_test1
参数列表
as
自定义代码段
调用执行：exec 存储过程名称

create proc usp_TwoNumberAdd
@num1 int,
@num2 int
as
begin
select @num1+@num2
end
----存储过程传参数有几种方式
----第一种方式
declare @n1 int=45,@n2 int=43
exec usp_TwoNumberAdd @num1=@n1,@num2=@n2
----第二种方式
exec usp_TwoNumberAdd 21,20

---模糊查询存储过程用户传入宇，和年龄>20返回来有多少条数据，并把这些数据显示出来

create proc usp_SlectStuByNameAndAge
@name nvarchar(10),--名字
@age int,--年龄
@count int output --条数
as
begin
set @count=(select count(*) from Student where StuName like @name+'%' and StuAge>@age)
select * from Student where StuName like @name+'%' and StuAge>@age
end
declare @ct int
exec usp_SlectStuByNameAndAge '宇',15,@ct output
select @ct

分页的存储过程

---第几页
---每页多少条
---总页数返回来
create proc usp_pageUser
@page int,--页数
@count int,--条数
@sumPage int output--总页数
as
begin
set @sumPage=(CEILING((select count(*) from TblUsers)*1.0/@count))--总页数
select * from
(select 编号=ROW_NUMBER()over(order by AutoId),* from TblUsers)as tu
where tu.编号 between (@page-1)*@count+1 and @page*@count
end

---第4页，每页6条
declare @i int
exec usp_pageUser 4,6,@i output
select @i

触发器

作用：对表进行增、删、改操作时，自动进行一个操作

根据触发机制不同，分为：after触发器，instead of替换触发器
创建触发器：
create trigger 名称
On 表名
[After|instead of] [insert|delete|update]
As
Begin
…
end
两个临时表：inserted、deleted
示例：删除分公司时，将该分公司的所有部门都删除
建议：对于性能影响太大，所以要慎重使用

SQL Server sql处理数据库锁的存储过程分享

感兴趣的小伙伴，下面一起跟随小编 jb51.cc的小编两巴掌来看看吧！

邹建 2004.4

代码如下:

 
/*--调用示例 
exec p_lockinfo1 
--*/ 
alter proc p_lockinfo1 
@kill_lock_spid bit=1,--是否杀掉死锁的进程,1 杀掉,0 仅显示 
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示 
as 
declare @count int,@s nvarchar(max),@i int 
select id=identity(int,1,1),标志,进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计cpu时间=cpu,登陆时间=login_time,打开事务数=open_tran,进程状态=status,工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,域名=nt_domain,网卡地址=net_address 
into #t from( 
select 标志='死锁的进程',spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address,s1=a.spid,s2=0 
from master..sysprocesses a join ( 
select blocked from master..sysprocesses group by blocked 
)b on a.spid=b.blocked where a.blocked=0 
union all 
select '|_牺牲品_>',blocked,s1=blocked,s2=1 
from master..sysprocesses a where blocked<>0 
)a order by s1,s2 

select @count=@@rowcount,@i=1 

if @count=0 and @show_spid_if_nolock=1 
begin 
insert #t 
select 标志='正常的进程',db_name(dbid),net_address 
from master..sysprocesses 
set @count=@@rowcount 
end 

if @count>0 
begin 
create table #t1(id int identity(1,a nvarchar(max),b Int,EventInfo nvarchar(max)) 
if @kill_lock_spid=1 
begin 
declare @spid varchar(max),@标志 varchar(max) 
while @i<=@count 
begin 
select @spid=进程ID,@标志=标志 from #t where id=@i 
insert #t1 exec('dbcc inputbuffer('+@spid+')') 
if @标志='死锁的进程' exec('kill '+@spid) 
set @i=@i+1 
end 
end 
else 
while @i<=@count 
begin 
select @s='dbcc inputbuffer('+cast(进程ID as varchar(max))+')' from #t where id=@i 
insert #t1 exec(@s) 
set @i=@i+1 
end 
select a.*,进程的sql语句=b.EventInfo 
from #t a join #t1 b on a.id=b.id 
end 
go

Sql Server 导出数据库表结构的SQL查询语句

 1 --导出数据库所有表
 2 
 3 SELECT
 4      表名       = Case When A.colorder=1 Then D.name Else '''' End,
 5      表说明     = Case When A.colorder=1 Then isnull(F.value,'''') Else '''' End,
 6      字段序号   = A.colorder,
 7      字段名     = A.name,
 8      字段说明   = isnull(G.[value],''''),
 9      标识       = Case When COLUMNPROPERTY( A.id,A.name,''IsIdentity'')=1 Then ''√''Else '''' End,
10      主键       = Case When exists(SELECT 1 FROM sysobjects Where xtype=''PK'' and parent_obj=A.id and name in (
11                       SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then ''√'' else '''' end,
12      类型       = B.name,
13      占用字节数 = A.Length,
14      长度       = COLUMNPROPERTY(A.id,A.name,''PRECISION''),
15      小数位数   = isnull(COLUMNPROPERTY(A.id,A.name,''Scale''),0),
16      允许空     = Case When A.isnullable=1 Then ''√''Else '''' End,
17      默认值     = isnull(E.Text,'''')
18  FROM
19      syscolumns A
20  Left Join
21      systypes B
22  On
23      A.xusertype=B.xusertype
24  Inner Join
25      sysobjects D
26  On
27      A.id=D.id  and D.xtype=''U'' and  D.name<>''dtproperties''
28  Left Join
29      syscomments E
30  on
31      A.cdefault=E.id
32  Left Join
33  sys.extended_properties  G
34  on
35      A.id=G.major_id and A.colid=G.minor_id
36  Left Join
37  
38  sys.extended_properties F
39  On
40      D.id=F.major_id and F.minor_id=0
41      --where d.name=''OrderInfo''    --如果只查询指定表,加上此条件
42  Order By
43      A.id,A.colorder

Sql Server 数据库表结构，存储过程，视图比较脚本

顶级干货

用来比较两个数据库之间表结构，存储过程及视图差异的存储过程，直接复制对应的存储过程，无需改动，直接在数据库中执行（传递要比较的数据库参数）即可

1. 两个数据库之间存储过程及视图差异比较的存储过程

--测试脚本
--exec [p_compSPAndView] ''FAMS_PrePROD'',''FAMS_SIT''  
 
 
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE?proc
 [dbo].[p_compSPAndView] 

@db1
 sysname, --第一个库


@db2
 sysname --第二个库


as

exec(''


select
 类型=case isnull(a.xtype,b.xtype) when ''''V''''
 then N''''视图''''
 else N''''存储过程''''
 end 

,匹配情况=case


when
 a.name is null then N''''库
 [''+@db1+'']
 中无''''


when
 b.name is null then N''''库
 [''+@db2+'']
 中无''''


else
 N''''结构不同''''
 end 

,对象名称=isnull(a.name,b.name),a.text
 as atext, b.text as btext

from(


select
 a.name,a.xtype,b.colid,b.text 

from
 [''+@db1+'']..sysobjects
 a,[''+@db1+'']..syscomments
 b 

where
 a.id=b.id and a.xtype in(''''V'''',''''P'''')
 and a.status>=0 

)a
 full join( 

select
 a.name,a.xtype,b.colid,b.text 

from
 [''+@db2+'']..sysobjects
 a,[''+@db2+'']..syscomments
 b 

where
 a.id=b.id and a.xtype in(''''V'''',''''P'''')
 and a.status>=0 

)b
 on a.name=b.name and a.xtype=b.xtype and a.colid=b.colid 

where
 a.name is null 

or
 b.name is null 

or
 isnull(a.text,'''''''')
 <>isnull(b.text,'''''''')
'')


--group by a.name,b.name,a.xtype,b.xtype 

--order by 类型,匹配情况,对象名称
GO

View Code

效果如下：

2. 两个数据库之间表结构差异比较的存储过程

--测试脚本
--exec p_comparestructure ''Inventory_SR08'',''Inventory_SR08_bk''

-- =============================================
-- Author:
-- Description:    <比较两个数据库的表结构差异并自动生成修改和插入表字段脚本>
--exec p_comparestructure ''Inventory_SR08'',''Inventory_SR08_bk''
-- =============================================
Create proc [dbo].[p_comparestructure]
@dbname1 varchar(250),--要比较的数据库名1
@dbname2 varchar(250)--要比较的数据库名2
as
set @dbname1=''[''+@dbname1+'']''
set @dbname2=''[''+@dbname2+'']''
 
create table #tb1(表名1 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 sql_variant,字段说明 sql_variant)
 
create table #tb2(表名2 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 sql_variant,字段说明 sql_variant)
 
--得到数据库1的结构
exec(''insert into #tb1 SELECT 
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM ''+@dbname1+''.dbo.sysobjects where xtype=''''PK'''' and parent_obj=a.id and name in (
SELECT name FROM ''+@dbname1+''.dbo.sysindexes WHERE indid in(
SELECT indid FROM ''+@dbname1+''.dbo.sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
类型=b.name,占用字节数=a.length,长度=a.prec,小数位数=a.scale,允许空=a.isnullable,
默认值=isnull(e.text,''''''''),字段说明=isnull(g.[value],'''''''')
FROM ''+@dbname1+''.dbo.syscolumns a
left join ''+@dbname1+''.dbo.systypes b on a.xtype=b.xusertype
inner join ''+@dbname1+''.dbo.sysobjects d on a.id=d.id  and d.xtype=''''U'''' and  d.name<>''''dtproperties''''
left join ''+@dbname1+''.dbo.syscomments e on a.cdefault=e.id
left join ''+@dbname1+''.sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id  
order by a.id,a.colorder'')
--select * from csdntest.sys.extended_properties
--得到数据库2的结构
exec(''insert into #tb2 SELECT 
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM ''+@dbname2+''.dbo.sysobjects where xtype=''''PK'''' and parent_obj=a.id and name in (
SELECT name FROM ''+@dbname2+''.dbo.sysindexes WHERE indid in(
SELECT indid FROM ''+@dbname2+''.dbo.sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
类型=b.name,占用字节数=a.length,长度=a.prec,小数位数=a.scale,允许空=a.isnullable,
默认值=isnull(e.text,''''''''),字段说明=isnull(g.[value],'''''''')
FROM ''+@dbname2+''.dbo.syscolumns a
left join ''+@dbname2+''.dbo.systypes b on a.xtype=b.xusertype
inner join ''+@dbname2+''.dbo.sysobjects d on a.id=d.id  and d.xtype=''''U'''' and  d.name<>''''dtproperties''''
left join ''+@dbname2+''.dbo.syscomments e on a.cdefault=e.id
left join ''+@dbname2+''.sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id  
order by a.id,a.colorder'')
--and not exists(select 1 from #tb2 where 表名2=a.表名1)
select 比较结果=case when a.表名1 is null and b.序号=1 then @dbname1+''——缺少表：''+b.表名2
when b.表名2 is null and a.序号=1 then @dbname2+''——缺少表:''+a.表名1
when a.字段名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then 
''/*''+@dbname1+''——[''+b.表名2+''] 缺少字段：''+b.字段名+''*/alter table ''+b.表名2+'' drop column ''+b.字段名
when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) and a.类型=''decimal'' then 
''/*''+@dbname2+''——[''+a.表名1+''] 缺少字段：''+a.字段名+''*/ alter table ''+a.表名1+'' add ''+a.字段名+'' ''
+a.类型+''(''+ltrim(str(a.长度))+'',''+ltrim(str(isnull(a.小数位数,'''')))+'')''+(case when a.允许空=0 then '' not null'' else '' null'' end)
when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) and a.类型=''nvarchar'' then 
''/*''+@dbname2+''——[''+a.表名1+''] 缺少字段：''+a.字段名+''*/ alter table ''+a.表名1+'' add ''+a.字段名+'' ''
+a.类型+''(''+ltrim(str(a.长度))+'')''+(case when a.允许空=0 then '' not null'' else '' null'' end)
when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) and a.类型 not in(''nvarchar'',''decimal'') then 
''/*''+@dbname2+''——[''+a.表名1+''] 缺少字段：''+a.字段名+''*/ alter table ''+a.表名1+'' add ''+a.字段名+'' ''
+a.类型+(case when a.允许空=0 then '' not null'' else '' null'' end)
when a.标识<>b.标识 then ''--标识不同''
when a.主键<>b.主键 then ''--主键设置不同''
when a.类型<>b.类型 and a.类型=''decimal'' then 
''/*字段类型不同*/ alter table ''+a.表名1+'' alter column ''+a.字段名+'' ''+a.类型+''(''+ltrim(str(a.长度))+'',''+ltrim(str(isnull(a.小数位数,'''')))+'')''+(case when a.允许空=0 then '' not null'' else '' null'' end)
when a.类型<>b.类型 and a.类型=''nvarchar''  then 
''/*字段类型不同*/ alter table ''+a.表名1+'' alter column ''+a.字段名+'' ''+a.类型+''(''+ltrim(str(a.长度))+'')''+(case when a.允许空=0 then '' not null'' else '' null'' end)
when a.类型<>b.类型 and a.类型 not in(''nvarchar'',''decimal'')  then 
''/*字段类型不同*/ alter table ''+a.表名1+'' alter column ''+a.字段名+'' ''+a.类型+(case when a.允许空=0 then '' not null'' else '' null'' end)
when a.占用字节数<>b.占用字节数 then 
''/*占用字节数*/ alter table ''+a.表名1+'' alter column ''+a.字段名+'' ''+a.类型+''(''+ltrim(str(a.长度))+'')''+(case when a.允许空=0 then '' not null'' else '' null'' end)
when a.长度<>b.长度 then 
''/*长度不同*/ alter table ''+a.表名1+'' alter column ''+a.字段名+'' ''+a.类型+''(''+ltrim(str(a.长度))+'')''+(case when a.允许空=0 then '' not null'' else '' null'' end)
when a.小数位数<>b.小数位数 then 
''--小数位数不同*/ alter table ''+a.表名1+'' alter column ''+a.字段名+'' ''+a.类型+''(''+ltrim(str(a.长度))+'',''+ltrim(str(isnull(a.小数位数,'''')))+'')''+(case when a.允许空=0 then '' not null'' else '' null'' end)
when a.允许空<>b.允许空 then ''--是否允许空不同''
when a.默认值<>b.默认值 then 
''/*默认值不同*/ alter table ''+a.表名1+'' add default(0) for ''+a.字段名+'' with values''
--when isnull(a.字段说明,'''')<>'''' and isnull(b.字段说明,'''')='''' then 
--''EXEC sys.sp_addextendedproperty @name=N''''''+''MS_Description''+'''''', @value=N''''''+a.字段说明--+'''''' , @level0type=N''''''+''SCHEMA''+'''''',@level0name=N''''''+''dbo''+'''''', @level1type=N''''''+''TABLE''+'''''',@level1name=N''''''+b.表名2+'''''', @level2type=N''''''+''COLUMN''+'''''',@level2name=N''''''+b.字段名+''''''--字段说明不同''
else '''' end,
*
from #tb1 a
full join #tb2 b on a.表名1=b.表名2 and a.字段名=b.字段名
where a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null 
or a.标识<>b.标识 or a.主键<>b.主键 or a.类型<>b.类型
or a.占用字节数<>b.占用字节数 or a.长度<>b.长度 or a.小数位数<>b.小数位数
or a.允许空<>b.允许空 or a.默认值<>b.默认值-- or a.字段说明<>b.字段说明
order by isnull(a.表名1,b.表名2),isnull(a.序号,b.序号),isnull(a.字段名,b.字段名)

View Code

效果如下：取第一个为例，表名 1（FAMS_PRD_TRUNNK 数据库下）的 AMT_AssetRetirement 表有 HandleOption 字段，而 FAMS 数据库下的 AMT_AssetRetirement 表不存在 HandleOption 字段

我们今天的关于sqlserver获取数据库表结构的存储过程和sqlserver获取数据库名的分享就到这里，谢谢您的阅读，如果想了解更多关于MSSQLServer基础07（事务，存储过程，分页的存储过程,触发器）、SQL Server sql处理数据库锁的存储过程分享、Sql Server 导出数据库表结构的SQL查询语句、Sql Server 数据库表结构，存储过程，视图比较脚本的相关信息，可以在本站进行搜索。

本文标签：