江苏龙网

  1. 主页 > 生活百科 > >

SQL Server 常用近百条SQL语句( 六 )

SQL Server

 
69. 查询分区函数
select * from sys.partition_functions 
70. 查看分区架构
select * from sys.partition_schemes 
71. 查询ssis包的信息
select * from msdb.dbo.sysssispackages 
72. 查询某张表里的索引的大小,如下示例表为dbo.table1
SELECTi.nameAS 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 
73. 重建表上的所有索引
alter index all on table_name rebuild with (online=on)重建表上的某个索引alter index index_name on table_name rebuild with (online=on)重新组织表上的所有索引 alter index all on table_name reorganize重新组织表上的某个索引 alter index index_name on table_name reorganize 
74. 查看数据文件可收缩空间，结果见Availabesize_MB字段值
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_MBfrom sys.master_files where database_id=db_id(N'DBNAME')75. 查询某个表中的全部索引的信息 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
sqlserver中类似oracle的dba_source的视图是sys.sql_modules
 
76. 查询某个数据库下的表数据占用磁盘容量最大的10张表
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 begininsert into #tabNameexec sp_spaceused @namefetch 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 ( SELECTt.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 FROMsys.tables t INNER JOINsys.indexes i ON t.OBJECT_ID = i.object_id INNER JOINsys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOINsys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOINsys.schemas s ON t.schema_id = s.schema_id WHEREt.NAME NOT LIKE 'dt%'AND t.is_ms_shipped = 0AND i.OBJECT_ID > 255 GROUP BYt.Name, s.Name, p.Rows) a GROUP BYa.tablename,a.SCHEMANAME order by sum(a.TotalSpaceMB) desc --这个比上一个专业 77. 查询某个数据库中是否有create index '+name+ CHAR(10) 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; 
78. 根据id号查询某个数据库名


推荐阅读


上一篇：2020年最走心的经典句子短句?2020年最火的的句子

下一篇：libresse卫生巾感受?libresse薇尔卫生巾怎么样