SQL常用命令语句收集

sql server中有很多内置的命令，我们只需要调用即可，熟练使用这些语句可以很大提高我们的工作效率，下面是编者收集的一些常用命令语句，以后有新发现时会持续更新。

ALTER TABLE TableName Add 字段 类型 NOT NULL DEFAULT 0
--添加字段

ALTER TABLE TableName alter column 字段 类型 NOT NULL
--修改字段类型

exec sp_rename '[表名].[字段名]','新字段名'
--修改某个字段名称

SET IDENTITY_INSERT TableName [ ON | OFF ]
--说明：当某列设置为自增长时，如果想将值手动插入该列，就需要先将IDENTITY_INSERT设置为ON，再执行插入操作，最后关闭IDENTITY_INSERT。
注意：该方法不能修改自增长列的值，但可以间接的通过插入一条新记录，将原来的记录删除来实现。
--否则会有报错：
--仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时,才能为表'TableName'中的标识列指定显式值。
--当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'TableName' 中的标识列插入显式值。

TRUNCATE TABLE TableName
--清空表TableName并使得自增ID从1开始。

ALTER DATABASE DataBaseName SET [ SINGLE_USER | MULTI_USER | READ_ONLY ]
将数据库DataBaseName设置为单用户 | 多用户 | 只读模式。

WITH ROLLBACK IMMEDIATE
--执行这个命令之后，SQL Server立即断开所有的连接并回退它们的事务。所有正在执行事务的用户都会接收到一个连接错误，而且他们不能再连接数据库。

SET ANSI_NULLS [ ON | OFF ]
--Transact-SQL 支持在与空值进行比较时，允许比较运算符返回 TRUE 或 FALSE。通过设置 ANSI_NULLS OFF 可将此选项激活。
--当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。
当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。

SET QUOTED_IDENTIFIER [ ON | OFF ]
--当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。当 SET QUOTED_IDENTIFIER 为 OFF 时(双引号被解释为字符串的边界，和单引号的作用是类似的。)，标识符不可加引号，且必须遵守所有 Transact-SQL 标识符规则。

ORDER BY NEWID()
--从一个表中随机查询某些记录，可实现但当数据记录量太大时会导致效率低下。所以对于数据量较大的表就避免使用。

CHECKPOINT
--将当前数据库的全部脏页写入磁盘。“脏页”是已输入缓存区高速缓存且已修改但尚未写入磁盘的数据页。CHECKPOINT 可创建一个检查点，在该点保证全部脏页都已写入磁盘，从而在以后的恢复过程中节省时间。 

DBCC DROPCLEANBUFFERS
--从缓冲池中删除所有清除缓冲区。

DBCC FREEPROCCACHE
--从过程缓存中删除所有元素。

DBCC FREESYSTEMCACHE
--从所有缓存中释放所有未使用的缓存条目。SQL Server 2005 数据库引擎会事先在后台清理未使用的缓存条目，以使内存可用于当前条目。但是，可以使用此命令从所有缓存中手动删除未使用的条目。

DBCC OPENTRAN
--查看数据库打开事务状态等。

SET STATISTICS IO ON
--这是一个使SQL Server显示有关由 Transact-SQL 语句生成的磁盘活动量的信息的命令。其中的 lob 逻辑读取、lob 物理读取、lob 预读 这三个指标是 读取 text、ntext、image 或大值类型 (varchar(max)、nvarchar(max)、varbinary(max)) 时的指标。而逻辑读取、物理读取、预读是对普通数据页的读取。

sp_cursor_list
--查看全部游标。

select c.name from sysconstraints a 
inner join syscolumns b on a.colid=b.colid 
inner join sysobjects c on a.constid=c.id 
where a.id=object_id('Company') 
and b.name='ComStyle'  
--查表Company的ComStyle字段上的约束名

Alter table Company drop constraint DF_Company_ComStyle
--删除约束（DF_Company_ComStyle）

ALTER TABLE Company ADD CONSTRAINT DF_Company_ComStyle DEFAULT ('moban0') FOR ComStyle
--为表Company的ComStyle字段设置默认值（添加约束）

with(tablockx)
with(tablockx)和普通的SELECT
with(tablockx)先执行，普通的SELECT将等待前者完成.
普通的SELECT先执行，  with(tablockx)也将等待前者结束才执行，如果前者长时间不结束，with(tablockx)将失败。
with(tablockx)和带事务的SELECT或者with(tablockx) 
不管谁先执行，都要等待先执行的事务提交后，后一个才能执行。

select name from syscolumns where id=object_id(N'TableName')--查询列名
select name from sysobjects where xtype= 'U'--查询所有表名
select name from sysobjects where xtype= 'P'--查询所有存储过程

--查询数据库表中的说明字段
SELECT A.name AS table_name,B.name AS column_name,C.value AS column_description 
FROM sys.tables A INNER JOIN sys.columns B 
ON B.object_id = A.object_id LEFT JOIN sys.extended_properties C 
ON C.major_id = B.object_id AND C.minor_id = B.column_id 
WHERE A.name ='TableName'

--快速查看表结构
DECLARE @tableName NVARCHAR(MAX )
SET @tableName='TableName';--表名!!!

SELECT CASE WHEN col.colorder = 1 THEN obj.name
ELSE ''
END AS 表名,
col.colorder AS 序号 ,
col.name AS 列名 ,
ISNULL(ep.[value], '') AS 列说明 ,
t.name AS 数据类型 ,
col.length AS 长度 ,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'
ELSE ''
END AS 标识 ,
CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid ) THEN '√'
ELSE ''
END AS 主键 ,
CASE WHEN col.isnullable = 1 THEN '√'
ELSE ''
END AS 允许空 ,
ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype = 'U'
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = 'MS_Description'
WHERE obj.name = @tableName--表名
ORDER BY col.colorder ;

具体参照T-SQL丛书：https://docs.microsoft.com/en-us/sql/t-sql/statements/statements