日度归档:2019年9月4日

mssql sqlserver 使用sql脚本查询所有表及其字段的方法分享


摘要:
在sql健康检查时,我们常常需检测整个数据库拥有多少数据表及数据表下面有多少字段这样的需求,下文讲述使用sql脚本获取这些信息的方法,如下所示:
实验环境:sqlserver 2008 R2



实现思路:
1.通过查询 sysobjects 系统表,获取数据库下数据表数量
2.通过查询 syscolumns 系统表,获取数据列的相关信息

 ---获取数据库下数据表信息
   select * from sysobjects where xtype='u' 

 ---获取所有数据列信息
  select a.name as [数据列名称],
b.name as [数据表名称]  
from syscolumns  a 
left join sysobjects  b 
  on a.id=b.id  
   where b.xtype ='u'

—-参数说明:
xtype:
C:CHECK 约束
D:默认值或 DEFAULT 约束
F:FOREIGN KEY 约束
L :日志
FN :标量函数
IF:内嵌表函数
P:存储过程
PK :PRIMARY KEY 约束(类型是 K)
RF: 复制筛选存储过程
S : 系统表
TF :表函数
TR : 触发器
U : 用户表
UQ : UNIQUE 约束(类型是 K)
V : 视图
X : 扩展存储过程

mssql sqlserver sql脚本创建“表值函数”的方法分享


摘要:
下文讲述sqlserver创建表值函数的方法,如下所示:
实验环境:sqlserver 2008 R2


表值函数的功能

我们将sqlserver中函数返回值为一张数据表的自定义函数称之为“表值函数”。
由于表值函数是返回一张表,所以我们常常将表值函数返回表结果和cross apply计算中。


表值函数的创建方法

    go
create function dbo.fn_backTableTest
(@a int,@b int
)returns  @t table(h int,w int)  
as
begin 
  insert into @t(h,w)values
  (@a,@b)
  
  return  
end
go

---表值函数测试
select * from   dbo.fn_backTableTest(100,88)

go 
drop function dbo.fn_backTableTest
go

相关阅读:

使用APPLY运算符简介

mssql sqlserver 使用sql脚本计算两个日期之间的工作日及节假日天数的方法分享


摘要:
下文讲述使用sql脚本计算两个日期之间的工作日及节假日的方法
实验环境:sql server 2008 R2


实现思路:
1.设置一个法定节假日表(存储 劳动节 国庆节 端午节 春节等法定节假日数据)
2.编写一个sql 函数输入参数为: 起始日期和结束日期 输出信息为 工作日和节假日

---节假日数据表
create table [maomaoJieRi]
(dateJieRi date)
go
---基础数据生成
insert into [maomaoJieRi](dateJieRi)
values('2019-5-1'),('2019-5-2'),('2019-5-3')


--sql函数定义
go
create function dbo.fn_workDayAndHoliday
(@date1 datetime,@date2 datetime
)returns  @t table(hoildayCount int,weekDayCount int)  
as
begin 
   
  declare @h int  --节假日
  declare @w int  --工作日
  set @h =0;
  set @w =0


   while @date1<@date2
   begin
      if datename(weekday,@date1) <> N'星期六' 
	     and  datename(weekday,@date1) <> N'星期日' 
		 and not exists(select null from [maomaoJieRi] where dateJieRi=@date1)
	begin
	  set @w = @w+1 
	end
	else
	begin
	  set @h =@h+1 
	end

	set @date1 =dateadd(d,1,@date1)

   end

   insert into @t(hoildayCount,weekDayCount)
			values (@h,@w)
  return  
end
go

---数据测试

select * from   dbo.fn_workDayAndHoliday('2019-5-1','2019-5-8')

go 
drop function dbo.fn_workDayAndHoliday
go
truncate table [maomaoJieRi]
drop table [maomaoJieRi]