1、表结构文档生成查询语句:
复制代码
SELECT
架构名 = Case When A.colorder=1 Then s.[name] Else '' End,
表名 = Case When A.colorder=1 Then D.name Else '' 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,A.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(A.id,A.name,'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
left join sys.tables as t on t.[name]=D.name
left join sys.schemas as s on t.schema_id = s.schema_id
--where d.name='OrderInfo' --如果只查询指定表,加上此条件
--where s.[name]!='dbo' and s.[name] in ('HR') --架构名的查询范围
Order By s.[name],D.name,A.id,A.colorder
复制代码
2、数据库自动备份的脚本代码
复制代码
declare @diskPath varchar(250)
declare @name varchar(300)
set @diskPath='C:\Backup\MyLogSystem_'+
convert(varchar(100),getdate(),112)+ replace(CONVERT(varchar(100), GETDATE(), 108),':','')+'.bak'
set @name=N'MyLogSystem-完整数据库备份'
BACKUP DATABASE[MyLogSystem]TO
DISK=@diskPath
WITH NOFORMAT, NOINIT,
NAME = @name,
SKIP, NOREWIND, NOUNLOAD
复制代码
3、不同服务器间查询导入导出数据
复制代码
/*不同服务器数据库之间的数据操作*/
--创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '
--查询示例
select * from ITSV.数据库名.dbo.表名
--导入示例
select * into 表 from ITSV.数据库名.dbo.表名
--以后不再使用时删除链接服务器
exec sp_dropserver 'ITSV ', 'droplogins '
--连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset
--查询示例
select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
--生成本地表
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
--把本地表导入远程表
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
select *from 本地表
--更新本地表
update b
set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1
--openquery用法需要创建一个连接
--首先创建一个连接创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
--查询
select *
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
--把本地表导入远程表
insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A
--3、opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta
--把本地表导入远程表
复制代码
4、打开和关闭sql查询excel服务.
复制代码
要用拥有服务器sysadmin角色权限的登录帐号,
先执行下列语句打开SQL SERVER的'Ad Hoc Distributed Queries'开关。
exec sp_configure 'show advanced options',1;reconfigure;
exec sp_configure 'Ad Hoc Distributed Queries',1;reconfigure;
配置选项 'show advanced options' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。
配置选项 'Ad Hoc Distributed Queries' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。
执行查询后,再用相反顺序的语句,关闭'Ad Hoc Distributed Queries'开关。
exec sp_configure 'Ad Hoc Distributed Queries',0;reconfigure;
exec sp_configure 'show advanced options',0;reconfigure;
复制代码
5、PIVOT行转列sql函数
复制代码
--1、建测试表
create table Insurances (
RefID uniqueidentifier not null,
HRMS nvarchar(20) null,
Name nvarchar(20) null,
InsuranceMoney money null,
InsuranceName nvarchar(100) not null,
constraint PK_INSURANCES primary key (RefID)
)
--2、插入多条数据
insert into Insurances values (newid(),1,'张三',200,'养老保险')
insert into Insurances values (newid(),1,'张三',300,'医疗保险')
insert into Insurances values (newid(),2,'李四',250,'养老保险')
insert into Insurances values (newid(),2,'李四',350,'医疗保险')
insert into Insurances values (newid(),3,'王二',150,'养老保险')
insert into Insurances values (newid(),3,'王二',300,'医疗保险')
--3、聚合行转列查询
select HRMS,Name,InsuranceMoney,InsuranceName From Insurances
select * from
(
select HRMS,Name,InsuranceMoney,InsuranceName from Insurances
) p
Pivot (
sum(InsuranceMoney)
FOR InsuranceName IN
( [医疗保险], [养老保险]))
as pvt
--4、如果pivot函数不支持 需改变但求数据库的等级
sp_dbcmptlevel BBS, 100
复制代码
6、mssql查询区分大小写的语句
SELECT *
FROM TbUser
WHERE UserName='taobao2' collate Chinese_PRC_CS_AI_WS
7、SQl插入数据的同时返回主键值的语句
insert into dbo.BenefitItem
(BenefitItemNO,BenefitCategoryID,BenefitItemName,RecordStatus) values('flx006',3,'test7','Active')
select scope_identity()
8、查询表结构语句2
复制代码
SELECT
SO.name as 表名,
SC.name as 表列名,
SC.colid as 索引,
ST.name as 类型
FROM
sysobjects SO, -- 对象表
syscolumns SC, -- 列名表
systypes ST -- 数据类型表
WHERE
so.name='cf_popuplst' and
SO.id = SC.id
AND SO.xtype = 'U' -- 类型U表示表,V表示视图
AND SO.status >= 0 --加一个条件:SO.status >= 0,否则会将系统的临时表显示出来
AND SC.xtype = ST.xusertype
ORDER BY
SO.name, SC.colorder -- 按表名、列名排序
复制代码
9、SQL获取视图的Model(Entity)
复制代码
declare @TableName nvarchar(500)
set @TableName='Huashanlin_Talent_ProgressView' --不带架构名的视图名
select
'public '+
(
CASE (A.DataType)
when 'int' then 'int?'
when 'nvarchar' then 'string'
when 'varchar' then 'string'
when 'smalldatetime' then 'DateTime?'
when 'datetime' then 'DateTime?'
when 'decimal' then 'decimal?'
when 'money' then 'decimal?'
when 'bit' then 'bool?'
else 'error'
end
)
+' '+A.ColumnName+' { get; set; }' as ModelContent,
A.ColumnName,A.DataType,A.DataLength,A.Description,
A.IsNullable,A.IsPrimaryKey
from
(
select
col.COLUMN_NAME as ColumnName,
col.DATA_TYPE as DataType,
col.CHARACTER_OCTET_LENGTH as DataLength,
col.IS_NULLABLE as IsNullable,
ccu.CONSTRAINT_NAME as IsPrimaryKey,
de.value as Description
from INFORMATION_SCHEMA.COLUMNS col
left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
on ccu.TABLE_NAME=col.TABLE_NAME and ccu.COLUMN_NAME=col.COLUMN_NAME and ccu.CONSTRAINT_NAME like 'PK_%'
left join ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @TableName, 'column', default) as de
on col.COLUMN_NAME = de.objname COLLATE Chinese_PRC_CI_AS
where col.TABLE_NAME=@TableName
) as A
复制代码
10、SQL获取表的Model(Entity)
复制代码
declare @TableName varchar(50)
set @TableName='Huashanlin_Talent_ProgressData' --表名不需要带架构名
select
'public '+
(
CASE (A.DataType)
when 'int' then (
case
(
select top 1 ind.is_primary_key
from sys.index_columns ic
left join sys.indexes ind
on ic.object_id=ind.object_id and ic.index_id=ind.index_id and ind.name like 'PK_%'
where ic.object_id=A.object_id and ic.column_id=A.column_id
)
when 1 then 'int'
else 'int?'
end
)
when 'nvarchar' then 'string'
when 'varchar' then 'string'
when 'smalldatetime' then 'DateTime?'
when 'datetime' then 'DateTime?'
when 'bit' then 'bool?'
else 'error'
end
)
+' '
+A.ColumnName+' { get; set; }' as ModelContent,
(
select top 1 ind.is_primary_key
from sys.index_columns ic
left join sys.indexes ind
on ic.object_id=ind.object_id and ic.index_id=ind.index_id and ind.name like 'PK_%'
where ic.object_id=A.object_id and ic.column_id=A.column_id
) as IsPrimaryKey,
A.ColumnName,A.IsNullable,A.DataType
from
(
select
col.name as ColumnName,
col.max_length as DataLength,
col.is_nullable as IsNullable,
t.name as DataType,
obj.object_id,
col.column_id
from sys.objects obj
inner join sys.columns col
on obj.object_id=col.object_id
left join sys.types t
on t.user_type_id=col.user_type_id
where obj.name=@TableName
) as A
复制代码