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 Insurancesselect * 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.IsPrimaryKeyfrom(    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.DataTypefrom(    select    col.name as ColumnName,    col.max_length as DataLength,    col.is_nullable as IsNullable,    t.name as DataType,