sqlserver2008命令管理工具

查询没有主键的数据库表

select b.name 
from sysobjects b 
where xtype='U' and  b.name not in   
     
(
     select object_name(a.parent_obj)  
     from sysobjects a 
     where xtype='PK' 
)

 

查看数据库所有表的主键索引

SELECT  
	A.name 表名,
    B.name 索引名,
    D.index_column_id 列的序号,
    E.name 列名,
    F.name 列的类型,
	F.max_length 列的最大长度,
	F.precision 列的精度,
	F.scale 列的小数位数
FROM SYS.OBJECTS A
JOIN SYS.OBJECTS B ON B.parent_object_id = A.OBJECT_Id
JOIN SYS.INDEXES C ON C.OBJECT_Id = B.parent_object_id AND C.name = B.name
JOIN SYS.INDEX_COLUMNS D ON D.OBJECT_Id = C.OBJECT_Id AND D.index_id = C.index_id
JOIN SYS.COLUMNS E ON E.OBJECT_Id = A.OBJECT_Id AND E.column_id = D.column_id 
JOIN SYS.TYPES F ON E.user_type_id=F.user_type_id
ORDER BY A.name ASC 

查询数据库所有索引

SELECT 
	O.[object_id] TableId ,
	O.Name TableName,
	ISNULL(KC.[object_id],IDX.index_id) IndexId,
	IDX.Name IndexName,
	ISNULL(KC.type_desc,'Index') IndexType,
	IDXC.index_column_id Index_Column_id,
	C.Column_id ColumnID,
	C.Name ColumnName,
	CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
	WHEN 1 THEN 'DESC' 
	WHEN 0 THEN 'ASC' ELSE '' 
	END Sort,
	CASE 
	WHEN IDX.is_primary_key=1 THEN N'√'
	ELSE N'' END PrimaryKey,
	CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END [UQIQUE],
	CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END Ignore_dup_key,
	CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END Disabled,
	IDX.fill_factor Fill_factor,
	CASE WHEN IDX.is_padded=1 THEN N'√'ELSE N'' END Padded
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id
INNER JOIN sys.objects O ON O.[object_id]=IDX.[object_id]
INNER JOIN sys.columns C ON O.[object_id]=C.[object_id] AND O.type='U' AND O.is_ms_shipped=0 AND IDXC.Column_id=C.Column_id
--    INNER JOIN  -- 对于一个列包含多个索引的情况,只显示第1个索引信息
--    (
--        SELECT [object_id], Column_id, index_id=MIN(index_id)
--        FROM sys.index_columns
--        GROUP BY [object_id], Column_id
--    ) IDXCUQ
--        ON IDXC.[object_id]=IDXCUQ.[object_id]
--            AND IDXC.Column_id=IDXCUQ.Column_id
WHERE ISNULL(KC.type_desc,'Index')  <> 'PRIMARY_KEY_CONSTRAINT'
ORDER BY o.name ASC, IDX.Name ASC

查询所有索引

 

select indexs.Tab_Name as [表名],indexs.Index_Name as [索引名] ,indexs.[Co_Names] as [索引列],
        Ind_Attribute.is_primary_key as [是否主键],Ind_Attribute.is_unique AS [是否唯一键],
        Ind_Attribute.is_disabled AS [是否禁用]
from 
(
    select Tab_Name,Index_Name, [Co_Names]=stuff((select '，'+[Co_Name] 
    from
    ( 
        select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name 
        from sys.indexes ind
        inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
        inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
        inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
    ) t 
    where Tab_Name=tb.Tab_Name and Index_Name=tb.Index_Name 
    for xml path('')), 1, 1, '')
    from 
    (
        select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name 
        from sys.indexes ind
        inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
        inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
        inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
    )tb
    where Tab_Name not like 'sys%'
    group by Tab_Name,Index_Name
) indexs 
inner join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.name
order by indexs.Tab_Name


select indexs.Tab_Name as [表名],indexs.Index_Name as [索引名] ,indexs.[Co_Names] as [索引列],
        Ind_Attribute.is_primary_key as [是否主键],Ind_Attribute.is_unique AS [是否唯一键],
        Ind_Attribute.is_disabled AS [是否禁用]
from 
(
    select Tab_Name,Index_Name, [Co_Names]=stuff((select '，'+[Co_Name] 
    from
    ( 
        select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name 
        from sys.indexes ind
        inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
        inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
        inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
    ) t 
    where Tab_Name=tb.Tab_Name and Index_Name=tb.Index_Name 
    for xml path('')), 1, 1, '')
    from 
    (
        select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name 
        from sys.indexes ind
        inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
        inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
        inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
    )tb
    where Tab_Name not like 'sys%'
    group by Tab_Name,Index_Name
) indexs 
inner join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.name
order by indexs.Tab_Name

数据库所有索引信息列表查询

 

SELECT 
	CASE WHEN C.column_id=1 THEN O.name ELSE N'' END TableName,
	ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N'') TableDesc,
	C.column_id Column_id,
	C.name ColumnName,
	ISNULL(IDX.PrimaryKey,N'') PrimaryKey,
	CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END [IDENTITY],
	CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END Computed,
	T.name Type,
	C.max_length Length,
	C.precision,
	C.scale,
	CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END NullAble,
	ISNULL(D.definition,N'') [Default],
	ISNULL(PFD.[value],N'') ColumnDesc,
	ISNULL(IDX.IndexName,N'') IndexName,
	ISNULL(IDX.Sort,N'') IndexSort,
	O.CreatCreate_Datee_Date,
	O.Modify_date Modify_Date
FROM sys.columns C
INNER JOIN sys.objects O ON C.[object_id]=O.[object_id]AND O.type='U' AND O.is_ms_shipped=0
INNER JOIN sys.types T ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D ON C.[object_id]=D.parent_object_id AND C.column_id=D.parent_column_id AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD ON PFD.class=1  AND C.[object_id]=PFD.major_id  AND C.column_id=PFD.minor_id
--AND PFD.name='Caption'  -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB ON PTB.class=1 AND PTB.minor_id=0  AND C.[object_id]=PTB.major_id
--AND PFD.name='Caption'  -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) 

LEFT JOIN                       -- 索引及主键信息
(
	SELECT IDXC.[object_id],IDXC.column_id,
		CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END Sort,
		CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END PrimaryKey,
		IDX.Name IndexName
	FROM sys.indexes IDX
	INNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id
	LEFT JOIN sys.key_constraints KC ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id
	INNER JOIN  -- 对于一个列包含多个索引的情况,只显示第1个索引信息
	(
		SELECT [object_id], Column_id, index_id=MIN(index_id)
		FROM sys.index_columns
		GROUP BY [object_id], Column_id
	) IDXCUQ
	ON IDXC.[object_id]=IDXCUQ.[object_id]
	AND IDXC.Column_id=IDXCUQ.Column_id
	AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id] AND C.column_id=IDX.column_id
-- WHERE O.name=N'要查询的表'       -- 如果只查询指定表,加上此条件
ORDER BY O.name,C.column_id 

 

tempdb 数据或日志文件的大小和文件增长参数：

SELECT
    name AS FileName,
    size*1.0/128 AS FileSizeinMB,
    CASE max_size
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' =
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

查询所有存储过程

select Pr_Name as [存储过程], [参数]=stuff(
(
	select '，'+[Parameter]
	from 
	(
		select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
		from sys.procedures Pr 
		left join sys.parameters parameter on Pr.object_id = parameter.object_id
		inner join sys.types Type on parameter.system_type_id = Type.system_type_id
		where type = 'P'
	) t 
	where Pr_Name=tb.Pr_Name 
	for xml path('')), 1, 1, ''
)
from 
(
	select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
	from sys.procedures Pr 
	left join sys.parameters parameter on Pr.object_id = parameter.object_id
	inner join sys.types Type on parameter.system_type_id = Type.system_type_id
	where type = 'P'
)tb
where Pr_Name not like 'sp_%' --and Pr_Name not like 'dt%'
group by Pr_Name
order by Pr_Name

查询所有的存储过程

select Pr_Name as [存储过程], [参数]=stuff(
(
	select '，'+[Parameter]
	from 
	(
		select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
		from sys.procedures Pr 
		left join sys.parameters parameter on Pr.object_id = parameter.object_id
		inner join sys.types Type on parameter.system_type_id = Type.system_type_id
		where type = 'P'
	) t 
	where Pr_Name=tb.Pr_Name 
	for xml path('')), 1, 1, ''
)
from 
(
	select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
	from sys.procedures Pr 
	left join sys.parameters parameter on Pr.object_id = parameter.object_id
	inner join sys.types Type on parameter.system_type_id = Type.system_type_id
	where type = 'P'
)tb
where Pr_Name not like 'sp_%' --and Pr_Name not like 'dt%'
group by Pr_Name
order by Pr_Name

 

存储过程信息查询

select Pr.Name as Pr_Name,parameter.name,T.Name,convert(varchar(32),parameter.max_length) as 参数长度,parameter.is_output as 是否是输出参数,parameter.*
from sys.procedures Pr 
left join sys.parameters parameter on Pr.object_id = parameter.object_id
inner join sys.types T on parameter.system_type_id = T.system_type_id
where Pr.type = 'P' and Pr.Name like 'order_%' and T.name!='sysname' 
order by Pr.Name

显示存储过程内容

SELECT TEXT 
FROM syscomments 
WHERE id=object_id('SP_NAME')

SP_HELPTEXT 'SP_NAME'

查询所有触发器

select triggers.name as [触发器],tables.name as [表名],triggers.is_disabled as [是否禁用],triggers.is_instead_of_trigger AS [触发器类型],
       case when triggers.is_instead_of_trigger = 1 then 'INSTEAD OF'when triggers.is_instead_of_trigger = 0 then 'AFTER' else null end as [触发器类型描述]
from sys.triggers triggers
inner join sys.tables tables on triggers.parent_id = tables.object_id
where triggers.type ='TR'
order by triggers.create_date

 批量删除视图

DECLARE @tb     VARCHAR(1000) ,
        @a      INT,
        @b      INT,
        @sql    VARCHAR(8000)
        
--第一步，读取所有视图
SELECT IDENTITY(INT,1,1) flag,[name] names
INTO #tmp
FROM sysobjects
WHERE xtype='v'
--第二步循环删除

SELECT @a=MIN(flag),@b=MAX(flag)
FROM #tmp

WHILE @a <= @b
    BEGIN
        SELECT @tb=names
        FROM #tmp
        WHERE flag=@a
        
        SET @sql='drop view dbo.'+ @tb
        EXEC(@sql)
        
        SET @a += 1
    END 

检查需要重建索引的表

SELECT OBJECT_NAME(dt.object_id),
        si.name,
        dt.avg_fragmentation_in_percent,
        dt.avg_page_space_used_in_percent
FROM
    (SELECT object_id,
        index_id,
        avg_fragmentation_in_percent,
        avg_page_space_used_in_percent
        FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
        WHERE   index_id <> 0
    ) AS dt --does not return information about heaps
        INNER JOIN sys.indexes si
            ON    si.object_id = dt.object_id
            AND si.index_id  = dt.index_id

生成维护索引语句

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' + 
    CASE WHEN ps.avg_fragmentation_in_percent > 15 
    THEN 'REBUILD' 
    ELSE 'REORGANIZE' 
    END + 
    CASE WHEN pc.partition_count > 1 
    THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX)) 
    ELSE '' 
    END, avg_fragmentation_in_percent 
FROM sys.indexes AS ix 
INNER JOIN sys.tables t ON t.object_id = ix.object_id 
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id 
INNER JOIN 
(
    SELECT object_id , index_id , avg_fragmentation_in_percent, partition_number 
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) 
) ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id 
INNER JOIN 
(
    SELECT object_id, index_id , COUNT(DISTINCT partition_number) AS partition_count 
    FROM sys.partitions 
    GROUP BY object_id, index_id 
) pc ON t.object_id = pc.object_id AND ix.index_id = pc.index_id 
WHERE ps.avg_fragmentation_in_percent > 10 AND ix.name IS NOT NULL 

--对数据库所有表重新生成索引语句

 

DECLARE @TableName VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name 
FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT 
FROM TableCursor 
INTO @TableName

WHILE @@FETCH_STATUS = 0
	BEGIN
		DBCC DBREINDEX(@TableName,' ',90)
		
		FETCH NEXT 
		FROM TableCursor 
		INTO @TableName
	END
CLOSE TableCursor
DEALLOCATE TableCursor

 

查询占用cpu资源的情况

 

select lastwaittype,spid,blocked,cpu,hostname,program_name     
from master.dbo.sysprocesses
order by cpu desc

统计出SQL Server所有的监控对象和计数器情况。

 

SELECT object_name,COUNT(DISTINCT counter_name)
FROM sys.dm_os_performance_counters
GROUP BY object_name
ORDER BY object_name

 

查询CPU占用情况

 

SELECT TOP 50
total_worker_time/execution_count AS '每次执行占用CPU(微秒)',
execution_count       as '执行次数',
total_worker_time     as '总共占用CPU(微秒)',
creation_time         as '创建时间',
last_execution_time   as '最后执行时间',
min_worker_time       as '最低每次占用CPU',
max_worker_time       as '最高每次占用cpu',
total_physical_reads  as '总共io物理读取次数',
total_logical_reads   as '总共逻辑读取次数',
total_logical_writes  as '总共逻辑写次数',
total_elapsed_time    as '完成此计划的执行所占用的总时间(微秒)',
(
    SELECT SUBSTRING(text,statement_start_offset/2,
    (
        CASE WHEN statement_end_offset = -1 
        then LEN(CONVERT(nvarchar(max), text)) * 2 
        ELSE statement_end_offset 
        end -statement_start_offset)/2
    ) 
    FROM sys.dm_exec_sql_text(sql_handle)
) AS 'SQL内容'
FROM sys.dm_exec_query_stats
ORDER BY 1 DESC

查询sqlser当前执行的对象

select request_session_id spid,
       OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks
where resource_type = 'OBJECT'