--查询数据库连接数

SELECT * FROM master.dbo.sysprocesses WHERE dbid IN

(

SELECT dbid FROM master.dbo.sysdatabases

WHERE NAME='数据库表名'

)

--查询锁表

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName

from sys.dm_tran_locks where resource_type='OBJECT'

ORDER BY request_session_id

--杀掉进程

declare @spid int

Set @spid = 182 --锁表进程

declare @sql varchar(1000)

set @sql='kill '+cast(@spid as varchar)

exec(@sql)

--远程查询

--开户外围

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

-------------------------------

select * from openrowset( 'SQLOLEDB', '服务器ip'; '数据库登录账号'; '数据库登录密码',数据库名.dbo.表名)

-------------------------------

--关闭外围

exec sp_configure 'show advanced options',0

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',0

reconfigure

--压缩备份

Declare @strDataBaseName varchar(50)

Declare @FullFileName varchar(50)

Set @strDataBaseName='data'

Set @FullFileName='d:\db_backup\data20170222.bak'
BACKUP DATABASE @strDataBaseName TO DISK = @FullFileName WITH INIT, STATS = 20

--sql 事务

BEGIN TRAN 事物名

IF(@@ERROR<>0)BEGIN ROLLBACK TRAN  事物名;RETURN;END

COMMIT TRAN  事物名

--数据库清缓存

DBCC DROPCLEANBUFFERS

--忽略自增列 按照sql语句的id新增

SET IDENTITY_INSERT [dbo].[EduMenuFunction] ON

--插入语句

SET IDENTITY_INSERT [dbo].[EduMenuFunction] OFF

--修改语句联表修改

UPDATE    S_GB_StudentBasicInfo

SET BH =ci.BH

from S_GB_StudentBasicInfo sbi  WITH(NOLOCK),C_GB_ClassInfo ci WITH(NOLOCK)

where sbi.BJID=CONVERT(nvarchar(20),ci.ID)

UPDATE    A

SET A.c2 =B.c3

from A inner join B on A.c1=B.c1

--创建索引

CREATE INDEX DataReportIndex

 ON S_GB_StudentBasicInfo_DataReport(SFZJH (ASC), XB (ASC), CSRQ (ASC), DataYear (ASC), STATUS (ASC))

--修改字段默认值

alter table 表名 drop constraint 约束名字   ------说明：删除表的字段的原有约束

alter table 表名 add constraint 约束名字 DEFAULT 默认值 for 字段名称 -------说明：添加一个表的字段的约束并指定默认值

--修改字段名：

alter table 表名 rename column A to B

--修改字段类型：

alter table 表名 alter column UnitPrice decimal(18, 4) not null 

--增加字段：

alter table 表名 ADD 字段 类型 NOT NULL Default 0

--添加字段 [CardRecord][Birthday]

IF NOT EXISTS( SELECT * from syscolumns where [id]=object_id('CardRecord') AND

 [name]='Birthday' )

 BEGIN

Alter Table CardRecord Add Birthday DATETIME NOT NULL DEFAULT('1900-01-01 00:00:00') ;

EXEC sp_addextendedproperty 'MS_Description', '出生日期', 'user', dbo, 'table',CardRecord, 'column', Birthday;

 END

 --清除表数据，自增列从1开始

 truncate table 表名;

--查询所有的子节点

with cte as

(

select * from Base_Company where Id = 'E7F5395E-6D11-4490-B3AD-513574268CF6' --查询节点

union all

select a.*

from Base_Company a join cte b on a.ParentId = b.id

where a.id is not null

)

select * from cte

--创建数据库

USE master

create database AdayMS

 on  primary  -- 默认就属于primary文件组,可省略

 (

 /*--数据文件的具体描述--*/

     name='AdayMS_data',  -- 主数据文件的逻辑名称

     filename='E:\数据库\jaday\AdayMS\AdayMS_data.mdf', -- 主数据文件的物理名称

     size=5mb, --主数据文件的初始大小

     maxsize=100mb, -- 主数据文件增长的最大值

     filegrowth=15%--主数据文件的增长率

 )

 log on

 (

 /*--日志文件的具体描述,各参数含义同上--*/

     name='AdayMS_log',

     filename='E:\数据库\jaday\AdayMS\AdayMS_log.ldf',

     size=2mb,

     filegrowth=1mb

 )

--根据时间筛选

datediff(dd,时间字段,当前时间)>=0

--获取新增数据的标识列

insert语句;select @@IDENTITY

--获取数据库表和视图中的所有字段

SELECT

列序号 = A.COLORDER,

列名 = A.NAME,

--主键 = 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,

--允许空 = CASE WHEN A.ISNULLABLE=1 THEN '√ 'ELSE ' ' END,

列说明 = ISNULL(G.[VALUE], ' ')

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

--视图

SELECT

列序号 = A.COLORDER,

列名 = A.NAME,

--主键 = 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,

--允许空 = CASE WHEN A.ISNULLABLE=1 THEN '√ 'ELSE ' ' END,

列说明 = ISNULL(G.[VALUE], ' ')

FROM SYSCOLUMNS A

LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE

INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND d.xtype='V' 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='@ViewName' )

GROUP BY

A.COLORDER

,A.NAME

,B.NAME

,ISNULL(G.[VALUE], ' ')

,A.ID

ORDER BY

A.ID,A.COLORDER

--触发器

ALTER TRIGGER [TRG_QBItem]

---------项触发器-------------------

ON [dbo].[QBItem]

FOR delete

AS

delete from QBOptions where ItemId in (select ItemId from deleted)

--计算执行sql语句的时间

--首先 在各个sql 执行语句前加：

declare @time datetime

set @time=getdate()

--然后在sql执行语句后加上：

select [语句执行花费时间(毫秒)]=datediff(ms,@time,getdate())