select SERVERPROPERTY ('propertyname')

select * from sys.configurations where name='XX'

sp_configure 'XX','0' RECONFIGURE WITH OVERRIDE

select * 
from sys.all_objects 
--查询当前数据库的所有架构范围的对象


select * 
from sys.sysobjects 
--查询当前数据库的所有对象


--sys.all_objects、sys.sysobjects 这种的视图，在每个数据库的系统视图下面都有






select * 
from sys.databases 
--在当前数据库下可以查询到所有数据库信息，包含是否on状态


select * 
from sys.sysdatabases 
--在当前数据库下可以查询到所有数据库信息，不包含是否on状态，这个系统视图会在后续的版本中删除


--sys.databases、sys.sysdatabases这种的视图，在每个数据库的系统视图下面都有

sys.processes 
--没有这个视图


select * 
from sys.sysprocesses 
--在当前数据库下可以查询所有正在SQL Server 实例上运行的进程的相关信息，也就是所有数据库上的线程，这个系统视图会在后续的版本中删除

sys.database_files --每个存储在数据库本身中的数据库文件在表中占用一行。这是一个基于每个数据库的视图。sys.master_files --master 数据库中的每个文件对应一行。这是一个系统范围视图。--sys.database_files、sys.master_files这种的视图，在每个数据库的系统视图下面都有

dbo.backupsetdbo.log_shipping_secondarydbo.restorehistorydbo.sysjobsdbo.sysjobhistory--这些系统表只存在msdb数据库，使用的时候必须加上msdb前缀

sp_lock

sp_helptext pro_name

DBCC INPUTBUFFER

DBCC INPUTBUFFER (249)

DBCC OPENTRAN (dbname)

DBCC SQLPERF (LOGSPACE)

select name,log_reuse_wait_desc from sys.databases

DBCC LOGINFO

dbcc checkdb (msdb);

CHECKPOINT [ checkpoint_duration ]--checkpoint_duration表示以秒为单位指定手动检查点完成所需的时间，一般不使用这个参数，让数据库自己控制

select name,State,user_access,is_read_only,recovery_model from sys.databases

select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('dbname')

select * from sys.sysprocesses where blocked>0或SELECT t1.resource_type,db_name(t1.resource_database_id),t1.resource_associated_entity_id,t1.request_mode,t1.request_session_id,t2.blocking_session_id,t2.wait_duration_msFROM sys.dm_tran_locks as t1INNER JOIN sys.dm_os_waiting_tasks as t2ON t1.lock_owner_address = t2.resource_address;或select A.SPID as 被阻塞进程,a.CMD AS 正在执行的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执行的操作from master..sysprocesses a,master..sysprocesses bwhere a.blocked<>0 and a.blocked= b.spid或SELECT session_Id,spid,ecid,DB_NAME (sp.dbid),nt_username,er.status,wait_type,[Individual Query] =SUBSTRING (qt.text,er.statement_start_offset / 2,( CASEWHEN er.statement_end_offset = -1THENLEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2ELSEer.statement_end_offsetEND- er.statement_start_offset)/ 2),qt.text,program_name,Hostname,nt_domain,start_timeFROM sys.dm_exec_requests erINNER JOIN sys.sysprocesses sp ON er.session_id = sp.spidCROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qtWHERE session_Id > 50 /* Ignore system spids.*/AND sp.blocked>0 AND session_Id NOT IN (@@SPID)或SELECT session_id ,status ,blocking_session_id,wait_type ,wait_time ,wait_resource,transaction_idFROM sys.dm_exec_requestsWHERE status = N'suspended';--sys.dm_exec_requests返回SQL Server 中正在执行的每个请求的信息

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableNamefrom sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_session_id ASC

select * from msdb.dbo.sysjobs where name='jobname'select a.program_name,a.* from master..sysprocesses a where a.program_name like '%0D1CE57E8AC5%'--把第一个语句查询到的job_id代入第二个语句的program_name

IF EXISTS (SELECT TOP 1 1FROM sys.sysprocessesWHERE program_name = 'SQLAgent - Generic Refresher')SELECT 'Running'ELSESELECT 'Not Running'

select 'KILL '+CAST(a.spid AS NVARCHAR(100)) AS KillCmd,REPLACE(hostname,' ','') as hostname ,replace(program_name,' ','') as program_name,REPLACE(loginame, ' ', '') AS loginame, db_name(a.dbid) AS DBname,spid,blocked,waittime/1000 as waittime,a.status,Replace(b.text,'''','''') as sqlmessage,cpufrom sys.sysprocesses as a with(nolock)cross apply sys.dm_exec_sql_text(sql_handle) as bwhere a.status<>'sleeping' AND a.spid<>@@SPID

SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASE WHEN estimated_completion_time < 36000000THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining],b.text as tsql,*FROM SYS.DM_EXEC_REQUESTScross apply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE 'Backup%' --and database_id=db_id('cardorder')--OR command LIKE 'RESTORE%'ORDER BY 2 DESC

SELECT DB_NAME(database_id) 
AS Exec_DB

,percent_complete

,
CASE 
WHEN estimated_completion_time < 
36000000


THEN 
'0' 
ELSE 
'' 
END + 
RTRIM(estimated_completion_time/
1000/
3600)

+ 
':' + 
RIGHT(
'0' + 
RTRIM((estimated_completion_time/
1000)%
3600/
60), 
2)

+ 
':' + 
RIGHT(
'0' + 
RTRIM((estimated_completion_time/
1000)%
60), 
2) 
AS [
Time Remaining]

,b.text 
as tsql

,*


FROM SYS.DM_EXEC_REQUESTS


cross 
apply sys.dm_exec_sql_text(sql_handle) 
as b


WHERE command 
LIKE 
'RESTORE%' 
--and database_id=db_id('cardorder')


--OR command LIKE 'RESTORE%'


ORDER 
BY 
2 
DESC

SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type备注：D 表示全备份，i 表示差异备份，L 表示日志备份

SELECTCONVERT(CHAR(100),SERVERPROPERTY('Servername'))AS Server,bs.database_name,bs.backup_start_date,bs.backup_finish_date,bs.expiration_date,CASE bs.typeWHEN 'D' THEN 'Database'WHEN 'L' THEN 'Log'END AS backup_type,bs.backup_size,bmf.logical_device_name,bmf.physical_device_name,bs.name AS backupset_name,bs.description,'RESTORE DATABASE ['+bs.database_name+'] FROM DISK=N'''+bmf.physical_device_name+ '''WITH NORECOVERY;'FROM msdb.dbo.backupmediafamily bmfINNER JOIN msdb.dbo.backupset bsON bmf.media_set_id=bs.media_set_idWHERE bs.backup_start_date>DATEADD(DAY,-1,GETDATE())ORDER BY bs.backup_finish_date

SELECT TOP 1000       S.database_name [Database],       CASE [S].[type]             WHEN 'L'             THEN N'RESTORE LOG ' + QUOTENAME(S.database_name) + N' FROM DISK = ''' + F.physical_device_name + N''' WITH NORECOVERY;'       END [LogRestore],       F.physical_device_name,       S.[Type],       S.backup_start_date,       S.backup_finish_date FROM msdb.dbo.backupmediafamily F INNER JOIN msdb.dbo.backupset S ON S.media_set_id = F.media_set_id WHERE S.database_name = 'XX' AND       S.type = 'L' AND S.backup_start_date > 'YYYY-MM-DD' ORDER BY S.backup_start_date ASC

select dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc from sys.dm_hadr_database_replica_states d join sys.availability_databases_cluster dc on d.group_database_id=dc.group_database_id and d.is_local=1

SELECTdb_name(database_id),mirroring_state_desc,mirroring_role_desc,mirroring_partner_name,mirroring_partner_instanceFROM sys.database_mirroring

SELECTb.name AS JobName, e.name, e.path, d.description, a.SubscriptionID, laststatus, eventtype, LastRunTime, date_created, date_modifiedFROMReportServer.dbo.ReportSchedule aJOIN msdb.dbo.sysjobs b ON CONVERT(SYSNAME,a.ScheduleID) = b.nameJOIN ReportServer.dbo.ReportSchedule c ON b.name = CONVERT(SYSNAME,c.ScheduleID)JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionIDJOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemidWHEREe.name = 'Report Name Goes Here'

SELECT db_name(database_id),* FROM master.sys.master_files WHERE database_id =DB_ID(N'DBA');

select b.name,a.type_desc,a.name,a.physical_name,a.size,a.max_size,a.is_percent_growth,a.growth from sys.master_files a join sys.databases b on a.database_id=b.database_id and a.physical_name like '%DTSWonda_1%'

SELECT sum(size*8/1024/1024) FROM master.sys.master_files

SELECT a.size*8/1024/1024 ,a.* FROM master.sys.master_files a WHERE physical_name like 'G:\DEFAULT.DATA%'

SELECT b.name dbname,a.size*8/1024/1024 sum_GB,a.type_desc,a.name datafilename,a.physical_name FROM master.sys.master_files a join sys.sysdatabases b on a.database_id=b.dbid and a.physical_name like 'G:\DEFAULT.DATA%' and a.size*8/1024/1024>8

SELECT

DB_NAME(db.database_id) DatabaseName,

(
CAST(mfrows.RowSize 
AS 
FLOAT)*
8)/
1024 RowSizeMB,

(
CAST(mflog.LogSize 
AS 
FLOAT)*
8)/
1024 LogSizeMB,

(
CAST(mfstream.StreamSize 
AS 
FLOAT)*
8)/
1024 StreamSizeMB,

(
CAST(mftext.TextIndexSize 
AS 
FLOAT)*
8)/
1024 TextIndexSizeMB


FROM sys.databases db


LEFT 
JOIN (
SELECT database_id, 
SUM(
size) RowSize 
FROM sys.master_files 
WHERE 
type = 
0 
GROUP 
BY database_id, 
type) mfrows 
ON mfrows.database_id = db.database_id


LEFT 
JOIN (
SELECT database_id, 
SUM(
size) LogSize 
FROM sys.master_files 
WHERE 
type = 
1 
GROUP 
BY database_id, 
type) mflog 
ON mflog.database_id = db.database_id


LEFT 
JOIN (
SELECT database_id, 
SUM(
size) StreamSize 
FROM sys.master_files 
WHERE 
type = 
2 
GROUP 
BY database_id, 
type) mfstream 
ON mfstream.database_id = db.database_id


LEFT 
JOIN (
SELECT database_id, 
SUM(
size) TextIndexSize 
FROM sys.master_files 
WHERE 
type = 
4 
GROUP 
BY database_id, 
type) mftext 
ON mftext.database_id = db.database_id

SELECT TOP 3total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],SUBSTRING(qt.text,qs.statement_start_offset/2+1,(CASE WHEN qs.statement_end_offset = -1THEN DATALENGTH(qt.text)ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)AS [使用CPU的语法], qt.text [完整语法],qt.dbid, dbname=db_name(qt.dbid),qt.objectid,object_name(qt.objectid,qt.dbid) ObjectNameFROM sys.dm_exec_query_stats qs WITH(nolock)CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qtWHERE execution_count>1 and last_execution_time>dateadd(dd,-5,getdate())ORDER BY total_worker_time DESC

SELECT TOP 
3

total_worker_time/
1000 
AS [总消耗CPU 时间(ms)],execution_count [运行次数],

qs.total_worker_time/qs.execution_count/
1000 
AS [平均消耗CPU 时间(ms)],

last_execution_time 
AS [最后一次执行时间],min_worker_time /
1000 
AS [最小执行时间(ms)],

max_worker_time /
1000 
AS [最大执行时间(ms)],


SUBSTRING(qt.text,qs.statement_start_offset/
2+
1,

(
CASE 
WHEN qs.statement_end_offset = 
-1


THEN 
DATALENGTH(qt.text)


ELSE qs.statement_end_offset 
END -qs.statement_start_offset)/
2 + 
1)


AS [使用CPU的语法], qt.text [完整语法],

qt.dbid, dbname=db_name(qt.dbid),

qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName


FROM sys.dm_exec_query_stats qs 
WITH(nolock)


CROSS 
apply sys.dm_exec_sql_text(qs.sql_handle) 
AS qt


WHERE execution_count>
1 
and last_execution_time>
dateadd(hh,
-5,
getdate())


ORDER 
BY (qs.total_worker_time/qs.execution_count/
1000) 
DESC

SELECT TOP 
10

session_id,request_id,start_time 
AS 
'开始时间',
status 
AS 
'状态',

command 
AS 
'命令',d_sql.text 
AS 
'sql语句', DB_NAME(database_id) 
AS 
'数据库名',

blocking_session_id 
AS 
'正在阻塞其他会话的会话ID',

wait_type 
AS 
'等待资源类型',wait_time 
AS 
'等待时间',wait_resource 
AS 
'等待的资源',


reads 
AS 
'物理读次数',writes 
AS 
'写次数',logical_reads 
AS 
'逻辑读次数',


row_count 
AS 
'返回结果行数'


FROM sys.dm_exec_requests 
AS d_request


CROSS 
APPLY

sys.dm_exec_sql_text(d_request.sql_handle) 
AS d_sql


WHERE session_id>
50


ORDER 
BY cpu_time 
DESC


--前50号session_id一般是系统后台进程，sys.dm_exec_requests的status显示为background

SELECT *FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )ON STP .job_id = JOB .job_idWHERE STP .command LIKE N'%sp_name%'--以上要查询某个job被哪个job调用了，把sp_name存储过程名字改成job_name作业名字即可

EXECUTE msdb.dbo.sp_start_job N'job_name'

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='表名' AND COLUMNPROPERTY(OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=1

SELECT IDENT_SEED ('表名')

SELECT IDENT_INCR('表名')

SELECT IDENT_CURRENT('表名')

DBCC CHECKIDENT (表名, RESEED, XX)

select SERVERPROPERTY('machinename'),@@SERVERNAME,SERVERPROPERTY ('edition'),@@version

use dbname exec sp_helprotect @username = 'username'

use dbname grant execute to "username"

SELECT * FROM  sys.dm_hadr_cluster_members;

select * from sys.dm_hadr_instance_node_map

SELECT * FROM SYS.dm_hadr_cluster;

select * from sys.dm_hadr_name_id_map

SELECT * FROM  sys.dm_hadr_cluster_networks;

select * from sys.availability_group_listeners;

select d.is_local,dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc from sys.dm_hadr_database_replica_states d join sys.availability_databases_cluster dc on d.group_database_id=dc.group_database_id;

select db_name(database_id),log_send_queue_size/
1024 delay_M,* 


from sys.dm_hadr_database_replica_states 
where is_primary_replica=
0;










select ar.replica_server_name, db_name(drs.database_id),drs.truncation_lsn, 

drs.log_send_queue_size, drs.redo_queue_size 


from sys.dm_hadr_database_replica_states drs 


join sys.availability_replicas ar 
on drs.replica_id=ar.replica_id 
where drs.is_local=
0;






select ar.replica_server_name, db_name(drs.database_id),drs.truncation_lsn, 

drs.log_send_queue_size,drs.log_send_rate, drs.redo_queue_size,drs.redo_rate 


from sys.dm_hadr_database_replica_states drs 


join sys.availability_replicas ar 
on drs.replica_id=ar.replica_id 
where drs.is_local=
0


--log_send_queue_size 主数据库中尚未发送到辅助数据库的日志记录量 (KB)


--log_send_rate 在最后一个活动期间，以千字节 (KB) 的平均主副本发送实例数据的速率/秒


--redo_queue_size 在最后一个活动期间，以千字节 (KB) 的平均主副本发送实例数据的速率/秒


--redo_rate 平均千字节 (KB) 中的给定辅助数据库做的日志记录速率 / 秒

SELECT  SERVERPROPERTY('FilestreamShareName')

select db_name(database_id),* from sys.database_filestream_options

select object_name(object_id),* from sys.filetables

SELECT FileTableRootPath()+[file_stream].GetFileNamespacePath(),name FROM testdb.dbo.table1

SELECT sj.Name,     CASE         WHEN sja.start_execution_date IS NULL THEN 'Not running'         WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'         WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'     END AS 'RunStatus' FROM msdb.dbo.sysjobs sj JOIN msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id WHERE session_id = (     SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) order by RunStatus desc;

TABLOCKX SELECT * FROM table WITH (TABLOCKX)

TABLOCK SELECT * FROM table WITH (TABLOCK)

HOLDLOCK SELECT * FROM table WITH (HOLDLOCK)

NOLOCK SELECT * FROM table WITH (NOLOCK)

select a.article,a.source_object,a.destination_object,b.colid from (select article,article_id,source_object,destination_object from [distribution].[dbo].MSarticles where publication_id in ( select publication_id from [distribution].[dbo].MSpublications where publication='XX' ) ) a inner join (select * from replicate1.dbo.sysarticlecolumns) b on a.article_id=b.artid order by a.article

select distinct article  from MSreplication_objects where publication='XX'

Select * from distribution.dbo.MSpublications

Select * from  distribution.dbo.MSarticles

select * from [distribution].[dbo].[MSlogreader_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].[MSdistribution_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].[MSsnapshot_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].MSrepl_errors order by 2 descselect * from msdb.dbo.sysreplicationalerts order by 7 desc

SELECT a.name index_name,c.name table_name,d.name column_name FROM sysindexes a JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid JOIN sysobjects c ON b.id=c.id JOIN syscolumns d ON b.id=d.id= AND b.colid=d.colid WHERE a.indid NOT IN(0,255) AND c.name in ('XX')

SET SHOWPLAN_ALL ON; GO select XXX GO SET SHOWPLAN_ALL OFF; GO 或 SET SHOWPLAN_XML ON; GO select XXX GO SET SHOWPLAN_XML OFF; GO

SELECT TOP 1 CONVERT(DATETIME, RTRIM(run_date))+ ((run_time / 10000 * 3600) + ((run_time % 10000) / 100 * 60) + (run_time % 10000) % 100) / (86399.9964) FROM msdb.dbo.sysjobhistory jobhis inner join msdb.dbo.sysjobs  jobs on jobhis.job_id = jobs.job_id AND jobhis.step_id = 0 AND jobhis.run_status = 1 and jobs.name='XXX' ORDER BY 1 DESC

exec sp_spaceused 'crm.EmailLog';

select convert(varchar(50), ps.name ) as partition_scheme, p.partition_number, convert(varchar(10), ds2.name ) as filegroup, convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, str(p.rows, 9) as rows from sys.indexes i join sys.partition_schemes ps on i.data_space_id = ps.data_space_id join sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id join sys.partitions p on dds.destination_id = p.partition_number and p.object_id = i.object_id and p.index_id = i.index_id join sys.partition_functions pf on ps.function_id = pf.function_id LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id and v.boundary_id = p.partition_number - pf.boundary_value_on_right WHERE i.object_id = object_id('crm.EmailLog') and i.index_id in (0, 1) order by p.partition_number

select * from sys.partition_functions

select * from sys.partition_schemes

select * from msdb.dbo.sysssispackages

SELECT     i.name              AS IndexName,     SUM(page_count * 8) AS IndexSizeKB FROM sys.dm_db_index_physical_stats(     db_id(), object_id('dbo.table1'), NULL, NULL, 'DETAILED') AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id GROUP BY i.name ORDER BY i.name

alter index all on table_name rebuild with (>

alter index index_name on table_name rebuild with (>

alter index all on table_name reorganize

alter index index_name on table_name reorganize

select name ,size*8/1024 as Totalsize_MB ,CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 as Usedsize_MB, size*8/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)*8/1024 AS Availabesize_MB  from sys.master_files where database_id=db_id(N'DBNAME')

declare @tableName 
varchar(
50) = 
'LbaListAlertDetail' 


declare @tableId 
int 






select @tableId = object_id 


from sys.objects 


where 
name = @tableName 






SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name 

       ,IX.name 
AS Index_Name 

       ,IX.type_desc Index_Type 

       ,
SUM(PS.[used_page_count]) * 
8 IndexSizeKB 

       ,IXUS.user_seeks 
AS NumOfSeeks 

       ,IXUS.user_scans 
AS NumOfScans 

       ,IXUS.user_lookups 
AS NumOfLookups 

       ,IXUS.user_updates 
AS NumOfUpdates 

       ,IXUS.last_user_seek 
AS LastSeek 

       ,IXUS.last_user_scan 
AS LastScan 

       ,IXUS.last_user_lookup 
AS LastLookup 

       ,IXUS.last_user_update 
AS LastUpdate 


FROM sys.indexes IX 


INNER 
JOIN sys.dm_db_index_usage_stats IXUS 
ON IXUS.index_id = IX.index_id 
AND IXUS.OBJECT_ID = IX.OBJECT_ID


INNER 
JOIN sys.dm_db_partition_stats PS 
on PS.object_id=IX.object_id 


WHERE OBJECTPROPERTY(IX.OBJECT_ID,
'IsUserTable') = 
1 

    
and IX.OBJECT_ID = @tableId 


GROUP 
BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update

use XX 


if 
exists(
select 
1 
from tempdb..sysobjects 
where 
id=object_id(
'tempdb..#tabName') 
and xtype=
'u') 


drop 
table 
#tabName 


go 


create 
table 
#tabName( 

table_name 
varchar(
100), 

rowsNum 
varchar(
100), 

reserved_size 
varchar(
100), 

data_size 
varchar(
100), 

index_size 
varchar(
100), 

unused_size 
varchar(
100) 

) 
 


declare @
name 
varchar(
100) 


declare cur 
cursor 
for 


select 
name 
from sysobjects 
where xtype=
'u' 
order 
by 
name 


open cur 


fetch 
next 
from cur 
into @
name 


while @@fetch_status=
0 


begin 

    
insert 
into 
#tabName 

    exec sp_spaceused @
name 

    
fetch 
next 
from cur 
into @
name 


end 


close cur 


deallocate cur 






select top 
10 table_name, data_size,rowsNum ,index_size,unused_size ,reserved_size,
convert(
int,
SUBSTRING(data_size,
0,
LEN(data_size)
-2)) 
size 


from 
#tabName ORDER BY size desc









或 


select top 
10 a.tablename,a.SCHEMANAME,
sum(a.TotalSpaceMB) TotalSpaceMB,
sum(a.RowCounts) RowCounts 


from ( 


SELECT 

    t.NAME 
AS TableName, 

    s.Name 
AS SchemaName, 

    p.rows 
AS RowCounts, 

    
SUM(a.total_pages) * 
8 
AS TotalSpaceKB, 

    
CAST(
ROUND(((
SUM(a.total_pages) * 
8) / 
1024.00), 
2) 
AS 
NUMERIC(
36, 
2)) 
AS TotalSpaceMB, 

    
SUM(a.used_pages) * 
8 
AS UsedSpaceKB, 

    
CAST(
ROUND(((
SUM(a.used_pages) * 
8) / 
1024.00), 
2) 
AS 
NUMERIC(
36, 
2)) 
AS UsedSpaceMB, 

    (
SUM(a.total_pages) - 
SUM(a.used_pages)) * 
8 
AS UnusedSpaceKB, 

    
CAST(
ROUND(((
SUM(a.total_pages) - 
SUM(a.used_pages)) * 
8) / 
1024.00, 
2) 
AS 
NUMERIC(
36, 
2)) 
AS UnusedSpaceMB 


FROM 

    sys.tables t 


INNER 
JOIN       

    sys.indexes i 
ON t.OBJECT_ID = i.object_id 


INNER 
JOIN 

    sys.partitions p 
ON i.object_id = p.OBJECT_ID 
AND i.index_id = p.index_id 


INNER 
JOIN 

    sys.allocation_units a 
ON p.partition_id = a.container_id 


LEFT 
OUTER 
JOIN 

    sys.schemas s 
ON t.schema_id = s.schema_id 


WHERE 

    t.NAME 
NOT 
LIKE 
'dt%' 

    
AND t.is_ms_shipped = 
0 

    
AND i.OBJECT_ID > 
255 


GROUP 
BY 

    t.Name, s.Name, p.Rows) a 


GROUP 
BY  a.tablename,a.SCHEMANAME 


order 
by 
sum(a.TotalSpaceMB) 
desc 


--这个比上一个专业 

select 'use '+name+ CHAR(10) +'select DB_NAME(),OBJECT_NAME(OBJECT_ID),definition from '+name+'.sys.sql_modulesWHERE objectproperty(OBJECT_ID, ''IsProcedure'') = 1 AND definition like ''%online%=%on%'' and definition like ''%index%''' from sys.databases;

SELECT DB_NAME(18)

SELECT OBJECT_NAME(1769220894)

SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASE WHEN estimated_completion_time < 36000000THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining],b.text as tsql,*FROM SYS.DM_EXEC_REQUESTScross apply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE 'DbccFilesCompact%' --and database_id=db_id('cardorder')ORDER BY 2 DESC

SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASE WHEN estimated_completion_time < 36000000THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining],b.text as tsql,*FROM SYS.DM_EXEC_REQUESTScross apply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE '%REORGANIZE%' --and database_id=db_id('cardorder')ORDER BY 2 DESC

SELECT         d.object_id ,         DB_NAME(d.database_id) DBName ,         OBJECT_NAME(object_id, database_id) 'SPName' ,         d.cached_time ,         d.last_execution_time ,         d.total_elapsed_time/1000000    AS total_elapsed_time,         d.total_elapsed_time / d.execution_count/1000000                                         AS [avg_elapsed_time] ,         d.last_elapsed_time/1000000     AS last_elapsed_time,         d.execution_count ,         d.total_physical_reads ,         d.last_physical_reads ,         d.total_logical_writes ,         d.last_logical_reads ,         et.text SQLText ,         eqp.query_plan executionplan FROM    sys.dm_exec_procedure_stats AS d CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) et CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) eqp WHERE   OBJECT_NAME(object_id, database_id) = 'xxxx' ORDER BY [total_worker_time] DESC;

select system_user

select * from Sys.traces