sqlserver --日常使用脚本

--------------------------------------------------------------------------------------------------------
--Description :sqlserver常用脚本：
--------------------------------------------------------------------------------------------------------
SELECT T.text, P.query_plan, S.host_name, S.program_name, S.client_interface_name, S.login_name, R.*
FROM sys.dm_exec_requests R
JOIN sys.dm_exec_sessions S on S.session_id=R.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T
CROSS APPLY sys.dm_exec_query_plan(plan_handle) As P
/
--磁盘使用情况
SELECT DISTINCT
volume_mount_point [Disk Mount Point],
file_system_type [File System Type],
logical_volume_name as [Logical Drive Name],
CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes
CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB],
CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]
FROM sys.master_files
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)
/
SQL Server日常维护常用的一些脚本整理。

1.sql server开启clr权限：

exec sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
ALTER DATABASE HWMESTC SET TRUSTWORTHY ON
ALTER AUTHORIZATION ON Database::HWMESTC TO sa;
--------------------------------------------------------------------------------------------------------
2.查询数据库大小

Exec sp_spaceused
select name, convert(float,size) * (8192.0/1024.0)/1024. from dbo.sysfiles
--------------------------------------------------------------------------------------------------------
3.数据库日志压缩

--选择需要使用的数据库
USE PIMS
--将数据库模式设置为SIMPLE
ALTER DATABASE PIMS SET RECOVERY SIMPLE
-- 将日志文件收缩到1M
DBCC SHRINKFILE ('PIMS_log', 1)
-- 还原数据库
ALTER DATABASE PIMS SET RECOVERY FULL
--------------------------------------------------------------------------------------------------------
4.查看数据库连接用户

Select * From sys.dm_exec_connections
--------------------------------------------------------------------------------------------------------
5.查看执行时间最长的sql
SELECT top 10
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
,SUBSTRING(
st.text,
(qs.statement_start_offset/2) + 1,
(
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2
) + 1
) N'执行语句'
,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE
SUBSTRING(
st.text,
(qs.statement_start_offset/2) + 1,
(
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2
) + 1
) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;
--------------------------------------------------------------------------------------------------------
6.查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局

SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc
--------------------------------------------------------------------------------------------------------
7.看BUFFER POOL中，都缓存了哪些表(当前数据库)的数据

select OBJECT_NAME(object_id) 表名,COUNT(*) 页数,COUNT(*)*8/1024.0 Mb
from sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c
where a.allocation_unit_id=b.allocation_unit_id
and b.container_id=c.hobt_id
and database_id=DB_ID()
group by OBJECT_NAME(object_id)
order by 2 desc
--------------------------------------------------------------------------------------------------------
8.查询SQLSERVER内存使用情况

select * from sys.dm_os_process_memory
--------------------------------------------------------------------------------------------------------
9.查询SqlServer总体的内存使用情况
select type,
sum(virtual_memory_reserved_kb)*0.1*10/1024/1024 as vm_Reserved_gb,--保留的内存
sum(virtual_memory_committed_kb)*0.1*10/1024/1024 as vm_Committed_gb,--提交的内存
sum(awe_allocated_kb)*0.1*10/1024/1024 as awe_Allocated_gb,--开启AWE后使用的内存
sum(shared_memory_reserved_kb)*0.1*10/1024/1024 as sm_Reserved_gb,--共享的保留内存
sum(shared_memory_committed_kb)*0.1*10/1024/1024 as sm_Committed_gb--共享的提交内存
from sys.dm_os_memory_clerks
group by type
order by type
--------------------------------------------------------------------------------------------------------
10.查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量
-- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量
-- 从这些信息可以看出，系统经常要访问的都是哪些表，有多大？
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*)
from sys.allocation_units a,
sys.dm_os_buffer_descriptors b,
sys.partitions p
where a.allocation_unit_id=b.allocation_unit_id
and a.container_id=p.hobt_id
and b.database_id=db_id()
group by p.object_id,p.index_id
order by buffer_pages desc
--------------------------------------------------------------------------------------------------------
11.查询缓存的各类执行计划，及分别占了多少内存

-- 查询缓存的各类执行计划，及分别占了多少内存
-- 可以对比动态查询与参数化SQL（预定义语句）的缓存量
select cacheobjtype
, objtype
, sum(cast(size_in_bytes as bigint))/1024 as size_in_kb
, count(bucketid) as cache_count
from sys.dm_exec_cached_plans
group by cacheobjtype, objtype
order by cacheobjtype, objtype
--------------------------------------------------------------------------------------------------------
12.查询缓存中具体的执行计划，及对应的SQL
-- 查询缓存中具体的执行计划，及对应的SQL
-- 将此结果按照数据表或SQL进行统计，可以作为基线，调整索引时考虑
-- 查询结果会很大，注意将结果集输出到表或文件中
SELECT usecounts ,
refcounts ,
size_in_bytes ,
cacheobjtype ,
objtype ,
TEXT
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO
--------------------------------------------------------------------------------------------------------
13.查询sql server内存整体使用情况

--查询sql server内存整体使用情况
SELECT object_name, cntr_value*0.1*10/1024/1024 ,cntr_value,cntr_type,t.counter_name,t.instance_name
FROM sys.dm_os_performance_counters t
WHERE counter_name = 'Total Server Memory (KB)';
--------------------------------------------------------------------------------------------------------
14.一次性清楚数据库所有表的数据
CREATE PROCEDURE sp_DeleteAllData
AS
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
GO
--------------------------------------------------------------------------------------------------------
15.SQL优化相关、执行时间
SELECT creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) NOT LIKE '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;
--------------------------------------------------------------------------------------------------------
16.truncate外键表存储过程

USE PIMS
GO

CREATE PROCEDURE [dbo].[usp_Truncate_Table]
@TableToTruncate VARCHAR(64)
AS

BEGIN

SET NOCOUNT ON

--==变量定义
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)

DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)

SET @Debug = 0--(0:将执行相关语句|1:不执行语句)
SET @Recycle = 0--(0:不创建/不清除存储表|1:将创建/清理存储表)
set @Verbose = 1--(1:每步执行均打印消息|0:不打印消息)

SET @i = 1
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'

-- 创建外键临时表
IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
DROP TABLE #FKs

-- 获取外键
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
OBJECT_NAME(constraint_object_id) as ConstraintName,
OBJECT_NAME(parent_object_id) as TableName,
clm1.name as ColumnName,
OBJECT_NAME(referenced_object_id) as ReferencedTableName,
clm2.name as ReferencedColumnName
INTO #FKs
FROM sys.foreign_key_columns fk
JOIN sys.columns clm1 ON fk.parent_column_id = clm1.column_id AND fk.parent_object_id = clm1.object_id
JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id AND fk.referenced_object_id= clm2.object_id
--WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
ORDER BY OBJECT_NAME(parent_object_id)

-- 外键操作(删除|重建)表
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
IF @Verbose = 1
PRINT '1. 正在创建表(Internal_FK_Definition_Storage)...'
CREATE TABLE [Internal_FK_Definition_Storage]
(
ID int not null identity(1,1) primary key,
FK_Name varchar(250) not null,
FK_CreationStatement varchar(max) not null,
FK_DestructionStatement varchar(max) not null,
Table_TruncationStatement varchar(max) not null
)
END
ELSE
BEGIN
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
TRUNCATE TABLE [Internal_FK_Definition_Storage]
END
ELSE
PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
END

IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '2. 正在备份外键定义...'
WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
BEGIN
SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)

SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)

INSERT INTO [Internal_FK_Definition_Storage]
SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp

SET @i = @i + 1

IF @Verbose = 1
PRINT ' > 已备份外键:[' + @ConstraintName + '] 所属表: [' + @TableName + ']'
END
END
ELSE
PRINT '2. 正在备份外键定义...'

IF @Verbose = 1
PRINT '3. 正在删除外键...'
BEGIN TRAN
BEGIN TRY
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > 已删除外键:[' + @ConstraintName + ']'
END

IF @Verbose = 1
PRINT '4. 正在清理数据表...'
--先清除该外键所在表(由于外键所在表仍可能又被其他外键所引用，因此需要循环递归处理)(注：本处理未实现)
--请不要使用下面注释代码
/*
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > ' + @Statement
END
*/

IF @Debug = 1
PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
ELSE
EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')
IF @Verbose = 1
PRINT ' > 已清理数据表[' + @TableToTruncate + ']'

IF @Verbose = 1
PRINT '5. 正在重建外键...'
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)
SET @i = @i + 1
IF @Verbose = 1
PRINT ' > 已重建外键:[' + @ConstraintName + ']'
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
PRINT '出错信息:'+ERROR_MESSAGE()
END CATCH
IF @Verbose = 1
PRINT '6. 处理完成！'
END
--------------------------------------------------------------------------------------------------------
17. 查看job运行持续时间

SELECT
[T1].[job_id]
,[T1].[name] AS [job_name]
,[T2].[run_status]
,[T2].[run_date]
,[T2].[run_time]
,[dbo].[agent_datetime]([T2].[run_date], [T2].[run_time]) AS [run_datetime]
,[T2].[run_duration]
,DATEDIFF(SECOND, '1900-01-01', DATEADD(SECOND, 31, [dbo].[agent_datetime](19000101, [run_duration]))) AS [run_duration_s]
FROM
[dbo].[sysjobs] AS T1
INNER JOIN [dbo].[sysjobhistory] AS T2
ON [T2].[job_id] = [T1].[job_id]
WHERE
[T1].[enabled] = 1
AND [T2].[step_id] = 0
AND [T2].[run_duration] >= 1
and [T1].[name]='PIMS_CreatePaperCraftParameterAnalysisData'
ORDER BY
[T2].[job_id] ASC
,[T2].[run_date] ASC
GO
--------------------------------------------------------------------------------------------------------
18. 从所有缓存中释放所有未使用的缓存条目

DBCC FREESYSTEMCACHE('ALL');
--------------------------------------------------------------------------------------------------------
19. 查询、解除死锁

--查询表死锁信息
select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_locks
where resource_type = 'OBJECT'

dbcc opentran

--查看死锁的详细信息、执行的sql语句
exec sp_who2 53
--exec sp_who 53
DBCC inputbuffer (53)

--解除死锁
kill 53
--------------------------------------------------------------------------------------------------------
20. 查询SQL Server根据CPU消耗列出前5个最差性能的查询

-- Worst performing CPU bound queries
SELECT TOP 5
st.text,
qp.query_plan,
qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC
GO
--------------------------------------------------------------------------------------------------------
21. 显示如何依据I/O消耗来找出你性能最差的查询

-- Worst performing I/O bound queries
SELECT TOP 5
st.text,
qp.query_plan,
qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_logical_reads DESC
GO
--------------------------------------------------------------------------------------------------------
22. 查询服务器部分特殊信息

select SERVERPROPERTY(N'edition') as Edition --数据版本，如企业版、开发版等
,SERVERPROPERTY(N'collation') as Collation --数据库字符集
,SERVERPROPERTY(N'servername') as ServerName --服务名
,@@VERSION as Version --数据库版本号
,@@LANGUAGE AS Language --数据库使用的语言，如us_english等
--------------------------------------------------------------------------------------------------------
23.查询数据库中各数据表大小
-- =============================================
-- 描 述：更新查询数据库中各表的大小，结果存储到数据表中
-- =============================================

--查询是否存在结果存储表
IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'temp_tableSpaceInfo') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
--不存在则创建
CREATE TABLE temp_tableSpaceInfo
(name NVARCHAR(128),
rows char(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18))
END
--清空数据表
DELETE FROM temp_tableSpaceInfo

--定义临时变量在遍历时存储表名称
DECLARE @tablename VARCHAR(255)

--使用游标读取数据库内所有表表名
DECLARE table_list_cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsTable') = 1 AND name NOT LIKE N'#%%' ORDER BY name

--打开游标
OPEN table_list_cursor
--读取第一条数据
FETCH NEXT FROM table_list_cursor INTO @tablename

--遍历查询到的表名
WHILE @@FETCH_STATUS = 0
BEGIN
--检查当前表是否为用户表
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
--当前表则读取其信息插入到表格中
EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo EXEC sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablename
END
--读取下一条数据
FETCH NEXT FROM table_list_cursor INTO @tablename
END

--释放游标
CLOSE table_list_cursor
DEALLOCATE table_list_cursor

SELECT *,replace(reserved,'KB','')/1024 数据表大小M FROM temp_tableSpaceInfo order by replace(reserved,'KB','')/1024 desc
drop table temp_tableSpaceInfo
--------------------------------------------------------------------------------------------------------
24.压缩数据库、文件、日志

DBCC ShrinkFile(‘数据库名’, targetsize); /* 收缩数据库文件 */
DBCC ShrinkFile(‘数据库名_log’, targetsize); /* 收缩日志文件 */
Targetsize：单位为兆，必须为整数，DBCC SHRINKFILE 尝试将文件收缩到指定大小。

DBCC SHRINKFILE 不会将文件收缩到小于“实际使用的空间”大小，例如“分配空间”为10M，“实际使用空间”为6M，当制定targetsize为1时，则将该文件收缩到6M，不会将文件收缩到1M。

--收缩数据库
DBCC SHRINKDATABASE(数据库名，百分比)
百分比：即“收缩后文件中的最大可用空间”，取值范围“大于等于0， 小于100%”，实际使用中设为0即可。
--------------------------------------------------------------------------------------------------------
25.用扩展时间抓取过去的死锁信息
View Code
--------------------------------------------------------------------------------------------------------
26.数据库对象信息检索

--查看对象的说明信息
exec sp_help 'T_papermachine'
--显示视图、存储过程、函数、触发器的定义脚本。
exec sp_helptext 'proc_report_getmeasuredata'
--显示表的行数和占用空间。
exec sp_spaceused 'T_papermachine'
--显示表或视图的前100行，选定“tablename,1000”按Ctrl+F1可显示表的前1000行。
exec sp_executesql N'IF OBJECT_ID(@tablename) IS NOT NULL EXEC(N''SELECT TOP(''+@n+N'') * FROM ''+@tablename)',N'@tablename nvarchar(100)=''t_papermachine'',@n int=100'
--显示表中每个索引占用的空间。
exec sp_executesql N'SELECT index_name = ind.name, ddps.used_page_count, ddps.reserved_page_count, ddps.row_count FROM sys.indexes ind INNER JOIN sys.dm_db_partition_stats ddps ON ind.object_id = ddps.object_id AND ind.index_id = ddps.index_id WHERE ind.object_id = OBJECT_ID(@tablename)',N'@tablename nvarchar(100)=''t_papermachine'''
--显示表或视图的字段名，以逗号分隔。
exec sp_executesql N'SELECT columns = STUFF((SELECT '', ''+name FROM sys.columns WHERE object_id = OBJECT_ID(@tablename) FOR XML PATH('''')),1,2,'''')',N'@tablename nvarchar(100)=''T_Papermachine'''
--根据选定关键词在当前数据库中查找表、视图、存储过程、函数
exec sp_executesql N'SELECT * FROM sys.objects WHERE type IN (''U'',''V'',''P'',''FN'') AND name LIKE ''%''+@keyword+''%'' ORDER BY type,name',N'@keyword nvarchar(50)=''machine'''
--查询数据库中包含指定关键词的表、视图、存储过程、函数
select routine_name,routine_definition,routine_type
from information_schema.routines
where routine_definition like '%AssessmentSpeed%'
order by routine_type
--模糊查询存储过程sql中包含某个文本
SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容
FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id = obj.ID
WHERE sc.TEXT LIKE '%存储过程内容%'
--------------------------------------------------------------------------------------------------------
27.数据库用户、权限操作

USE [master]
GO
--待确认账号密码
CREATE LOGIN [NDIT] WITH PASSWORD=N'1', DEFAULT_DATABASE=[PIMS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE PIMS
go
CREATE USER [NDIT] FOR LOGIN [NDIT]
GO
--大权限， 如果是指定的部分表，不执行这个，如果是所有内容都可以读，用此脚本
--EXEC sp_addrolemember N'db_datareader', N'NDIT'
--GO
--指定特定表名赋予新增/更新/查询
DECLARE @Sql NVARCHAR(max)
SET @Sql=''
--table
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.tables AS a WHERE name IN ('Tab1','Tab2');
--view
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.views AS a WHERE name IN ('view1','view2');
--procedure
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.procedures AS a WHERE name IN ('proc1','proc2');

PRINT @Sql

EXEC(@Sql)
go

--禁用登陆帐户
alter login NDIT disable
--启用登陆帐户
alter login NDIT enable

--登陆帐户改名
alter login NDIT with name=dba_tom

--登陆帐户改密码：
alter login NDIT with password='aabb@ccdd'

--数据库用户改名：
alter user NDIT with name=dba_tom

--更改数据库用户 defult_schema：
alter user NDIT with default_schema=sales

--删除数据库用户：
drop user NDIT

--删除 SQL Server登陆帐户：
drop login NDIT
--------------------------------------------------------------------------------------------------------
28.使用Checksum结合NewID获得随机数
Create FUNCTION Scalar_CheckSumNEWID
(
@From int,
@To int,
@Keep int,
@newid varchar(50)
)
RETURNS float
BEGIN
DECLARE @ResultVar float
SELECT @ResultVar=CONVERT(BIGINT,RIGHT(ABS(CHECKSUM(@newid)),9))*0.1/100000000
RETURN @From+round((@To-@From)*@ResultVar,@Keep)
END
GO
--------------------------------------------------------------------------------------------------------
29. 查询数据库表字段各项属性信息，便于直接复制导出excel表
SELECT
表名 = 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
--where d.name='OrderInfo' --如果只查询指定表,加上此条件
Order By
A.id,A.colorder
--------------------------------------------------------------------------------------------------------
30. 判断是否存在数据库、表、列、视图

1 判断数据库是否存在
if exists (select * from sys.databases where name = '数据库名')
drop database [数据库名]

2 判断表是否存在
if exists (select * from sysobjects where id = object_id(N'[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [表名]

3 判断存储过程是否存在
if exists (select * from sysobjects where id = object_id(N'[存储过程名]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [存储过程名]

4 判断临时表是否存在
if object_id('tempdb..#临时表名') is not null
drop table #临时表名

5 判断视图是否存在

--判断是否存在'MyView52'这个试图
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'MyView52')
PRINT '存在'
else
PRINT '不存在'
6 判断函数是否存在
-- 判断要创建的函数名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[函数名]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[函数名]

7 获取用户创建的对象信息

SELECT [name],[id],crdate FROM sysobjects where xtype='U'
8 判断列是否存在
if exists(select * from syscolumns where id=object_id('表名') and name='列名')
alter table 表名 drop column 列名

9 判断列是否自增列
if columnproperty(object_id('table'),'col','IsIdentity')=1
print '自增列'
else
print '不是自增列'

SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('表名') AND is_identity=1

10 判断表中是否存在索引

if exists(select * from sysindexes where id=object_id('表名') and name='索引名')
print '存在'
else
print '不存在'

删除索引 drop index 表名.索引名

或: drop index 索引名 on 表名(貌似2000不行)

11 查看数据库中对象

SELECT * FROM sys.sysobjects WHERE name='对象名' SELECT * FROM sys.sysobjects WHERE name='对象名'
--------------------------------------------------------------------------------------------------------
31. CTE查询的存储过程执行时间明显超出T-Sql查询。 可以通过添加“WITH RECOMPILE”参数，强制存储过程每次执行时重编译，实现快速查询。

大神的帖子： Parameter Sniffing, Embedding, and the RECOMPILE Options
--------------------------------------------------------------------------------------------------------
32. 解决insert exec 嵌套问题，解决办法是建立一个指向自己的数据库，增加链接服务器。

--1. 首先，增加链接服务器：

exec sp_addlinkedserver 'srv1','','SQLOLEDB','(local)'
exec sp_addlinkedsrvlogin 'srv1','false',null,'sa','sa'

--2. 其次找到该链接服务器，右键属性，开启RPC：

服务器对象->链接服务器->右键->属性->服务器选项->RPC、RPC Out 都设置为True

--3. 启动MSDTC服务：

服务名称为：MSDTC（显示名称为Distributed Transaction Coordinator）

如果没启动会报错如下：MSDTC on server 'servername' is unavailable

--4. 调整存储过程访问，使用srv1调用存储过程

insert #Temp exec srv1.DBName.dbo.Proc_Test @param

--5. 成功！结束！
--------------------------------------------------------------------------------------------------------
33. 查询数据库连接数、用户等

--查看连接到数据库"DB"的连接
SELECT * from master.dbo.sysprocesses WHERE dbid = DB_ID('DB')
--查询某个数据库用户的连接情况
sp_who 'sa'
--查看数据库允许的最大连接
select @@MAX_CONNECTIONS
--查看数据库自上次启动以来的连接次数
SELECT @@CONNECTIONS
--关闭连接，上面的查询可以得到spid，根据spid，关闭进程就可以了。
kill 54
--------------------------------------------------------------------------------------------------------
34当前正在执行的语句
select session_id,transaction_id,wait_type,last_wait_type,wait_resource,start_time,status,command
,estimated_completion_time,cpu_time,logical_reads,text,open_transaction_count,open_resultset_count,percent_complete
from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) s
where session_id>50 and session_id<>@@spid
go
--------------------------------------------------------------------------------------------------------
35堵塞语句
select spid,blocked,waittime,lastwaittype,waitresource,open_tran,status,p.dbid,cpu,physical_io,memusage,login_time,last_batch
,hostname,[program_name],hostprocess,cmd,nt_domain,nt_username,net_address,net_library,loginame,sql_handle,text
from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s
where blocked >0 or spid in(select sp.blocked from master.dbo.sysprocesses sp where sp.blocked>0)
go
--------------------------------------------------------------------------------------------------------
36是否有未提交事务
select spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran
,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text
from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s
where open_tran <> 0
go
--------------------------------------------------------------------------------------------------------
37各数据库连接数
SELECT @@ServerName AS server,NAME AS dbname,COUNT(STATUS) AS number_of_connections,GETDATE() AS timestamp
FROM sys.databases sd LEFT JOIN sys.sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME
GO
--------------------------------------------------------------------------------------------------------

38死锁跟踪，启用： dbcc traceon(1222,-1)

IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE ID=OBJECT_ID('TEMPDB..#deadlock'))
Drop TABLE #deadlock
CREATE TABLE #deadlock(LogDate DATETIME,ProcessInfo VARCHAR(20),Text VARCHAR(2000))
go
INSERT INTO #deadlock EXEC xp_readerrorlog 0,1,N'deadlock victim',N'','2018-03-01','2018-12-30','DESC'
go
SELECT DISTINCT 'exec xp_readerrorlog 0,1,NULL,NULL,'''+CONVERT(VARCHAR(19),LogDate,120)+''','''+CONVERT(VARCHAR(19),DATEADD(S,1,LogDate),120)+''',''ASC'''
FROM #deadlock
go
--------------------------------------------------------------------------------------------------------
39查看最近失败的SqlServer作业
select top 10 run_date,run_time,run_duration,step_name,message
from msdb..sysjobhistory where run_status = 0
order by run_date desc,run_time desc
go
--------------------------------------------------------------------------------------------------------
40各DB最近备份情况
SELECT database_name
,MAX(CASE WHEN type='D' THEN backup_finish_date ELSE NULL END) AS 完整备份时间
,MAX(CASE WHEN type='I' THEN backup_finish_date ELSE NULL END) AS 差异备份时间
,MAX(CASE WHEN type='L' THEN backup_finish_date ELSE NULL END) AS 日志备份时间
FROM(
SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date
FROM msdb.dbo.backupset
GROUP BY database_name,type
) T GROUP BY database_name
go
--------------------------------------------------------------------------------------------------------
41谁对对象进行了 DDL 操作 (exec sp_configure 'default trace enabled')
DECLARE @path NVARCHAR(1000)
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +'\log.trc'
FROM sys.traces WHERE id = 1
SELECT DatabaseID,NTDomainName,NTUserName,HostName, ClientProcessID,ApplicationName
,LoginName,StartTime,DatabaseName,ObjectName,SessionLoginName
,(CASE WHEN EventClass=46 THEN 'Object:Created' WHEN EventClass=47 THEN 'Object:Deleted' WHEN EventClass=164 THEN 'Object:Altered' END)EventClass
FROM ::fn_trace_gettable(@path, 0)
WHERE EventClass in(46,47,164) and DatabaseName<>'tempdb' and ObjectName is not null
GO
--------------------------------------------------------------------------------------------------------
42平均耗时最大的 SQL 语句
SELECT TOP 30 execution_count,total_worker_time,total_logical_reads,total_logical_writes
,total_worker_time/total_logical_reads as avgRead,s.text
FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) s
where execution_count>=100 and total_logical_reads<>0
order by avgRead desc
go
--------------------------------------------------------------------------------------------------------
43平均罗辑读最大的 SQL 语句
SELECT TOP 30 execution_count,total_worker_time,total_logical_reads,total_logical_writes
,total_worker_time/total_logical_reads as avgRead,s.text
FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) s
where execution_count<=10 and total_logical_reads<>0
order by avgRead desc
go
--------------------------------------------------------------------------------------------------------
44系统主要等待类型
SELECT TOP 10
wait_type,waiting_tasks_count ,wait_time_ms,signal_wait_time_ms
,wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
,100.0 * wait_time_ms / SUM (wait_time_ms ) OVER( )AS percent_total_waits
,100.0 * signal_wait_time_ms / SUM (signal_wait_time_ms) OVER( )AS percent_total_signal_waits
,100.0 * ( wait_time_ms - signal_wait_time_ms )/SUM (wait_time_ms ) OVER( ) AS percent_total_resource_waits
FROM sys .dm_os_wait_stats
WHERE wait_time_ms > 0
go
--------------------------------------------------------------------------------------------------------
45当前锁请求脚本
select req_spid
,case req_status when 1 then '已授予' when 2 then '正在转换' when 3 then '正在等待' end as req_status
,case rsc_type when 1 then 'NULL 资源（未使用）' when 2 then '数据库' when 3 then '文件'
when 4 then '索引' when 5 then '表' when 6 then '页' when 7 then '键'
when 8 then '扩展盘区' when 9 then 'RID（行 ID)' when 10 then '应用程序' else '' end rsc_type
,coalesce(OBJECT_NAME(rsc_objid),db_name(rsc_dbid)) as [object]
,case req_mode when 1 then 'NULL' when 1 then 'Sch-S' when 2 then 'Sch-M' when 3 then 'S'
when 4 then 'U' when 5 then 'X' when 6 then 'IS' when 7 then 'IU' when 8 then 'IX' when 9 then 'SIU'
when 10 then 'SIX' when 11 then 'UIX' when 12 then 'BU' when 13 then 'RangeS_S' when 14 then 'RangeS_U'
when 15 then 'RangeI_N' when 16 then 'RangeI_S' when 17 then 'RangeI_U' when 18 then 'RangeI_X'
when 19 then 'RangeX_S' when 20 then 'RangeX_U' when 21 then 'RangeX_X' else '' end req_mode
,rsc_indid as index_id,rsc_text,req_refcnt
,case req_ownertype when 1 then '事务' when 2 then '游标' when 3 then '会话' when 4 then 'ExSession' else'' end req_ownertype
from sys.syslockinfo WHERE rsc_type<>2
GO
--------------------------------------------------------------------------------------------------------
46
EXEC xp_enumerrorlogs 1 --查看 sqlserver 错误日志大小
EXEC xp_enumerrorlogs 2 --查看 代理日志大小
go
exec msdb.dbo.sp_cycle_errorlog -- "Sql Server 日志"切换
exec msdb.dbo.sp_cycle_agent_errorlog -- "代理错误日志"切换
go
--------------------------------------------------------------------------------------------------------
47各数据库日志大小及使用百分比
dbcc sqlperf(logspace)
go
--------------------------------------------------------------------------------------------------------
48当前DB虚拟日志数量
DBCC loginfo
go
--------------------------------------------------------------------------------------------------------
49数据库活动游标
DBCC activecursors
go
--------------------------------------------------------------------------------------------------------
50查看操作系统逻辑磁盘可用空间
EXEC master.dbo.xp_fixeddrives
go
--------------------------------------------------------------------------------------------------------
51数据库大小
select name,sum(size)*8/1024 from sys.database_files where type=0 group by name order by name
go

exec master.dbo.proc_getdbspaceused
go
--------------------------------------------------------------------------------------------------------
52数据库表大小及行数（部分不算太准确，但可作为参考）
SELECT OBJECT_NAME(id) as tab,rows,(reserved*8)/1024 as size_MB
FROM SYS.sysindexes WHERE indid IN(0,1) and id in(select object_id from sys.tables )
order by size_MB desc
go
--------------------------------------------------------------------------------------------------------
53数据库文件默认设置情况
select DB_NAME(database_id) as dbName,file_id,(size*8/1024) as [size(mb)]
,case when is_percent_growth = 1 then '10%' else CONVERT(varchar(10),growth*8/1024)+'M' end as growth
,type_desc,physical_name
from sys.master_files
where state = 0
go
--------------------------------------------------------------------------------------------------------
54各数据库 buffer pool 的分配情况
SELECT
CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS Database_name
,count(*) AS cached_pages_count
,count(*)*8/1024 AS cached_space_in_mb
,sum(convert(bigint,free_space_in_bytes))/1024/1024 AS free_space_in_mb
FROM sys.dm_os_buffer_descriptors(nolock)
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
GO
--------------------------------------------------------------------------------------------------------
55当前内存脏页数量及大小
SELECT db_name(database_id) AS 'Database'
,count(page_id) AS 'Dirty Pages'
,count(page_id)*8/1024 AS 'Dirty Pages(MB)'
FROM sys.dm_os_buffer_descriptors(nolock)
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY 'Dirty Pages' DESC
GO
--------------------------------------------------------------------------------------------------------
56缓存类型数量大小
select cacheobjtype as [Cached Type]
,COUNT(*) [Number of Plans]
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]
from sys.dm_exec_cached_plans
group by cacheobjtype
order by [Plan Cache Size(MB)] desc
GO
--------------------------------------------------------------------------------------------------------
57缓存对象数量大小
select objtype as [Cached Object Type]
,COUNT(*) as [Number of Plans]
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]
from sys.dm_exec_cached_plans
group by objtype
order by [Plan Cache Size(MB)] desc
GO
--------------------------------------------------------------------------------------------------------
58前N行则表示最近的N分钟内CPU使用情况

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK));
SELECT TOP(60)
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
,SQLProcessUtilization AS [SQL Server Process CPU Utilization]
,SystemIdle AS [System Idle Process]
,(100 - SystemIdle - SQLProcessUtilization) AS [Other Process CPU Utilization]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS[SystemIdle]
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%'
) AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
GO
-------------------------------------------------------------------------------------------
59复制相关
-------------------------------------------------------------------------------------------
--事务复制：未分发命令数(分发服务器执行)
SELECT 'EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds @publisher = N'''
+ a.publisher + ''', @publisher_db = N''' + a.publisher_db
+ ''', @publication = N''' + a.publication + ''', @subscriber = N'''
+ c.name + ''', @subscriber_db = N''' + b.subscriber_db
+ ''', @subscription_type =' + CAST(b.subscription_type AS VARCHAR)
FROM distribution.dbo.MSreplication_monitordata a ( NOLOCK )
INNER JOIN (
SELECT publication_id ,subscriber_id ,subscriber_db ,subscription_type
FROM distribution.dbo.MSsubscriptions (NOLOCK)
GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type
) b ON a.publication_id = b.publication_id
INNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id
WHERE a.agent_type = 1
go
--------------------------------------------------------------------------------------------------------
60查看前10个等待分发命令最多的事务数 及 查看命令
use distribution
go
SELECT top 10 A.xact_seqno,A.entry_time,COUNT(*) AS cmds
FROM distribution.dbo.MSrepl_transactions A(NOLOCK)
INNER JOIN distribution.dbo.MSrepl_commands B(NOLOCK)
ON A.xact_seqno=B.xact_seqno
GROUP BY A.xact_seqno,A.entry_time
ORDER BY cmds DESC
go
--------------------------------------------------------------------------------------------------------
61查看出现错误的事务序列号(历史记录) (分发服务器执行)
SELECT 'EXEC distribution.dbo.sp_helpsubscriptionerrors N'''
+ a.publisher + ''', N''' + a.publisher_db + ''', N''' + a.publication + ''', N''' + c.name + ''',N''' + b.subscriber_db + ''''
FROM distribution.dbo.MSreplication_monitordata a ( NOLOCK )
INNER JOIN (
SELECT publication_id ,subscriber_id ,subscriber_db ,subscription_type
FROM distribution.dbo.MSsubscriptions (NOLOCK)
GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type
) b ON a.publication_id = b.publication_id
INNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id
WHERE a.agent_type = 1
GO
--------------------------------------------------------------------------------------------------------
查询字段详细信息
use YCHMALL;
select schema_name(tab.schema_id) as schema_name,
tab.name as table_name,
col.name as column_name,
t.name as data_type,
t.name +
case when t.is_user_defined = 0 then
isnull('(' +
case when t.name in ('binary', 'char', 'nchar',
'varchar', 'nvarchar', 'varbinary') then
case col.max_length
when -1 then 'MAX'
else
case when t.name in ('nchar',
'nvarchar') then
cast(col.max_length/2
as varchar(4))
else cast(col.max_length
as varchar(4))
end
end
when t.name in ('datetime2', 'datetimeoffset',
'time') then
cast(col.scale as varchar(4))
when t.name in ('decimal', 'numeric') then
cast(col.precision as varchar(4)) + ', ' +
cast(col.scale as varchar(4))
end + ')', '')
else ':' +
(select c_t.name +
isnull('(' +
case when c_t.name in ('binary', 'char',
'nchar', 'varchar', 'nvarchar',
'varbinary') then
case c.max_length
when -1 then 'MAX'
else
case when t.name in
('nchar',
'nvarchar') then
cast(c.max_length/2
as varchar(4))
else cast(c.max_length
as varchar(4))
end
end
when c_t.name in ('datetime2',
'datetimeoffset', 'time') then
cast(c.scale as varchar(4))
when c_t.name in ('decimal', 'numeric') then
cast(c.precision as varchar(4)) + ', '
+ cast(c.scale as varchar(4))
end + ')', '')
from sys.columns as c
inner join sys.types as c_t
on c.system_type_id = c_t.user_type_id
where c.object_id = col.object_id
and c.column_id = col.column_id
and c.user_type_id = col.user_type_id
)
end as data_type_ext,
case when col.is_nullable = 0 then 'N'
else 'Y' end as nullable,
case when def.definition is not null then def.definition
else '' end as default_value,
case when pk.column_id is not null then 'PK'
else '' end as primary_key,
case when fk.parent_column_id is not null then 'FK'
else '' end as foreign_key,
case when uk.column_id is not null then 'UK'
else '' end as unique_key,
case when ch.check_const is not null then ch.check_const
else '' end as check_contraint,
cc.definition as computed_column_definition,
ep.value as comments
from sys.tables as tab
left join sys.columns as col
on tab.object_id = col.object_id
left join sys.types as t
on col.user_type_id = t.user_type_id
left join sys.default_constraints as def
on def.object_id = col.default_object_id
left join (
select index_columns.object_id,
index_columns.column_id
from sys.index_columns
inner join sys.indexes
on index_columns.object_id = indexes.object_id
and index_columns.index_id = indexes.index_id
where indexes.is_primary_key = 1
) as pk
on col.object_id = pk.object_id
and col.column_id = pk.column_id
left join (
select fc.parent_column_id,
fc.parent_object_id
from sys.foreign_keys as f
inner join sys.foreign_key_columns as fc
on f.object_id = fc.constraint_object_id
group by fc.parent_column_id, fc.parent_object_id
) as fk
on fk.parent_object_id = col.object_id
and fk.parent_column_id = col.column_id
left join (
select c.parent_column_id,
c.parent_object_id,
'Check' check_const
from sys.check_constraints as c
group by c.parent_column_id,
c.parent_object_id
) as ch
on col.column_id = ch.parent_column_id
and col.object_id = ch.parent_object_id
left join (
select index_columns.object_id,
index_columns.column_id
from sys.index_columns
inner join sys.indexes
on indexes.index_id = index_columns.index_id
and indexes.object_id = index_columns.object_id
where indexes.is_unique_constraint = 1
group by index_columns.object_id,
index_columns.column_id
) as uk
on col.column_id = uk.column_id
and col.object_id = uk.object_id
left join sys.extended_properties as ep
on tab.object_id = ep.major_id
and col.column_id = ep.minor_id
and ep.name = 'MS_Description'
and ep.class_desc = 'OBJECT_OR_COLUMN'
left join sys.computed_columns as cc
on tab.object_id = cc.object_id
and col.column_id = cc.column_id
order by schema_name,
table_name,
column_name;
--------------------------------------------------------------------------------------------------------
print '服务器的名称：'+@@SERVERNAME
print 'SQL Server的版本' + @@VERSION
SELECT @@SERVERNAME as '服务器名称'
select @@VERSION as 'SQL Server的版本'

数据库跨服务器设置：

EXEC sp_addlinkedserver
@server='DEV-W2K12-114', --链接服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='10.199.206.43' --要访问的的数据库所在的服务器的ip
GO
/
EXEC sp_addlinkedsrvlogin
'dblink_43', --链接服务器别名
'false',
NULL,
'Youcaihua', --要访问的数据库的用户
'DF170D23-9946-4340-BBCF-2A37845E8DD9' --要访问的数据库，用户的密码
GO

exec sp_dropserver'DEV-W2K12-114' ,'droplogins'

--------------------------------------------------------------------------------------------------------
use YCHMALL_RO;
update statistics Mall_OrderItem
/
DBCC SHOW_STATISTICS(Mall_OrderItem , _WA_Sys_00000018_6FB49575)
/
select * from sys.stats where OBJECT_NAME(object_id) = 'Mall_OrderItem';
--------------------------------------------------------------------------------------------------------