常用SQL:看全部表的空间大小|tempdb使用情况|清空日志|查看日志|查看数据库表|禁用所有约束|系统表|系统视图

时间:2022-06-01 16:37:34

看全部表的空间大小

我常用sp_spaceused来查看表所占的空间大小,可是一次只能看一个对象的。

今天有空写了个sp_spaceused2,可以同时看全部表的空间大小。

use master

go

EXEC sp_addmessage @msgnum = 55000, @severity = 16,

   @msgtext = N'Objects of type %s do not have space allocated.',

   @lang = 'us_english',@replace = 'replace'

EXEC sp_addmessage @msgnum = 55000, @severity = 16,

   @msgtext = N'没有为类型 %1! 的对象分配的空间。',

   @lang = '简体中文',@replace = 'replace'

go

if exists (select 1 from sysobjects where name = 'sp_spaceused2' and type = 'P')

drop procedure sp_spaceused2

go

create procedure sp_spaceused2 (

@type varchar(2) = 'U', -- The objects type we want size on.

@updateusage varchar(5) = false -- Param. for specifying that

     -- usage info. should be updated.

)

as

create table #spt_space

(

id  int not null primary key,

name  sysname,

rows  int null,

reserved dec(15) null,

data  dec(15) null,

indexp  dec(15) null,

unused  dec(15) null

)

--  Check the object type.

if @type not in ('U','S') -- no physical data storage.

  begin

   raiserror(55000,-1,-1,@type)

   return (1)

  end

--  Check to see if user wants usages updated.

if @updateusage is not null

begin

  select @updateusage=lower(@updateusage)

  if @updateusage not in ('true','false')

   begin

    raiserror(15143,-1,-1,@updateusage)

    return(1)

   end

end

if @updateusage = 'true'

begin

  dbcc updateusage(0) with no_infomsgs

  print ' '

end

set nocount on

--id, name, rows

insert into #spt_space (id, name, rows, data)

select i.id, o.name, i.rows, 0

from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid < 2 and o.type = @type

--reserved

update #spt_space

set reserved = r.reserved

from ( select o.id, sum(i.reserved) as reserved from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid in (0, 1, 255) and o.type = @type group by o.id) r

where #spt_space.id = r.id

--data

update #spt_space

set data = data + r.pages

from ( select o.id, isnull(sum(i.dpages),0) as pages from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid <2 and o.type = @type group by o.id) r

where #spt_space.id = r.id

update #spt_space

set data = data + r.used

from ( select o.id, isnull(sum(used), 0) as used from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid = 255 and o.type = @type group by o.id) r

where #spt_space.id = r.id

--index page

update #spt_space

set indexp = r.used - data

from ( select o.id, sum(convert(dec(15),used)) as used from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid in (0, 1, 255) and o.type = @type group by o.id) r

where #spt_space.id = r.id

--unused page

update #spt_space

set unused = reserved - r.used

from ( select o.id, sum(convert(dec(15),used)) as used from sysindexes i inner join sysobjects o on i.id = o.id

where i.indid in (0, 1, 255) and o.type = @type group by o.id) r

where #spt_space.id = r.id

--output

select #spt_space.name,

  rows = convert(char(11), rows),

  reserved = ltrim(str(reserved * d.low / 1024.,15,0) +

    ' ' + 'KB'),

  data = ltrim(str(data * d.low / 1024.,15,0) +

    ' ' + 'KB'),

  index_size = ltrim(str(indexp * d.low / 1024.,15,0) +

    ' ' + 'KB'),

  unused = ltrim(str(unused * d.low / 1024.,15,0) +

    ' ' + 'KB')

from #spt_space, master.dbo.spt_values d

  where d.number = 1

   and d.type = 'E'

return 0

go

sp_MS_marksystemobject 'sp_spaceused2'

tempdb使用情况

Select SUM (user_object_reserved_page_count)*8 as user_objects_kb,

SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,

SUM (version_store_reserved_page_count)*8 as version_store_kb,

SUM (unallocated_extent_page_count)*8 as freespace_kb

From sys.dm_db_file_space_usage

Where database_id = 2

--//清空日志

checkpoint

--//查看数据库日志

select * from ::fn_dblog(null,null)

select * from ::fn_dblog(null,null) where [current lsn]>='000001ae:00000074:0001' and [current lsn]<= '000001ae:00000085:0005'

--//2005中常用列

select [current lsn], [operation],[context],[transaction id],[allocunitname],[page id],[slot id],[rowlog contents 0] from ::fn_dblog(null,null)

--//2000中常用列

select [current lsn], [operation],[context],[transaction id],[object name],[page id],[slot id],[transaction name] from ::fn_dblog(null,null)

/*fn_dblog属性

Current LSN, Operation, Context, Transaction ID, Log Record Length, Object Name, Index Name, Page ID,

Offset in Row, Num Elements, Row Data, checkpoint Begin, Server UID, UID, SPID, Beginlog Status,

Transaction Name,

*/

--//查看所有日志详细信息

declare @sql varchar(100)

set @sql = 'dbcc log('+ db_name()+',3)'

exec(@sql)

/*

dbcc log ('DbSrc', 3, 'LSN', '0x0000006f:00000107:0001', 'XDESID', '0000:0000074d', 'LOGRECS', 'LOP_INSERT_ROWS')

数 据库名                 Current LSN                                                                                                                                trancation id                                                                        Operation   

*/

--//防止系统自动checkpoint

dbcc traceon(3505)

go

--//查看数据库某个页面的内容

dbcc traceon(3604)

go

dbcc page(dbName,fileID,pageID,3)

go

--//查看数据库表的所有信息

sp_help tablename

--//查看所有打开的开关

DBCC TRACESTATUS (-1) WITH NO_INFOMSGS   

--//也可用如下方法

--// Create the table to accept the results

CREATE TABLE #tracestatus (

TraceFlag INT,

Status INT

)

--// Execute the command, putting the results in the table

INSERT INTO #tracestatus

EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')

--// Display the results

SELECT *

FROM #tracestatus

GO

--//打开3604开关,只有打开该开关,才能执行dbcc page等其他相关指令

dbcc traceon(3604)

--//打开3505开关,该开关禁止自动checkpoint,只对当前连接有效

dbcc traceon(3505)

--//关闭3604开关

DBCC traceoff(3604)

--//备份日志采用两种不同模式

BACKUP LOG WITH TRUNCATE_ONLY 或 WITH NO_LOG

--//查看数据库版本

select @@version

select SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')        

--//查看用户设置

DBCC USEROPTIONS

--//修复表

sp_dboption '库名','SINGLE USER',TRUE --//设置单用户模式

go

DBCC CHECKTABLE('b_domestic_d_sam', REPAIR_REBUILD) --//修复

go

sp_dboption '库名', 'MULTI_USER', FALSE

go

--//或者如下

ALTER DATABASE 库名 SET 'SINGLE USER'

GO

DBCC CHECKDB('库名',repair_allow_data_loss) WITH TABLOCK

GO

ALTER DATABASE 库名 SET 'MULTI_USER'

GO

--//修复

DBCC CHECKDB('dbg1',repair_allow_data_loss)

--//查看索引信息

select index_col('db1',1,1)

--//查看数据库系适时状态

select * from master..sysprocesses

--//获取对象id

OBJECT_ID('b_domestic_d_sam')

--//设置专业连接开关

sp_configure 'remote admin connections', '1'

--//应用设置

reconfigure

--//控制台连接

osql -S192.168.2.189,1434 -Usa -P123456

--//创建索引

create index index_test on t_Identity_1(lsn,columnname);

--//查看表所分配的所有页

dbcc ind(库名称, 表名称, -1)

--//其他常用关键字

CASE

CHARINDEX

len

left

group by

order by

distinct

when

then

else

end

as

is

in

max

count

begin tran

commit tran

rollback tran

create table

create database

create index

--//常用系统表

sysindexes

sysobjects

syscolumns

sysindexkeys

systypes

--//查询目前分析日志需要的列信息

select name,xtype,length,xprec,xoffset,colid,bitpos,isnullable from syscolumns where id = (select id from sysobjects where name = 'sysobjects')

select name from syscolumns where id = object_id('tt') and colid in(select colid from sysindexkeys where id = object_id('tt'))   

select * from syscolumns where id =        (select id from sysobjects where xtype = 'U' and name = 'table1')   

--//设置自增列可插入(一个数据库中只能有一个可插入子增列)

SET IDENTITY_INSERT tablename on

--//设置自增列不可插入

SET IDENTITY_INSERT tablename on

--//阻止checkpoint

exec sp_dboption 'wdf100','trunc. log on chkpt.','off'

--//所有系统表

select * from dtproperties

select * from syscolumns

select * from syscomments

select * from sysdepends

select * from sysfilegroups

select * from sysfiles

select * from sysfiles1

select * from sysforeignkeys

select * from sysfulltextcatalogs

select * from sysfulltextnotify

select * from sysindexes

select * from sysindexkeys

select * from sysmembers

select * from sysobjects

select * from syspermissions

select * from sysproperties

select * from sysprotects

select * from sysreferences

select * from systypes

select * from sysusers

--//设置大对象存放在rowdata中

EXEC sp_dboption 'zhu', 'select into/bulkcopy', 'true'

EXEC sp_dboption 'zhu', 'select into/bulkcopy', 'false'

--//特殊操作

select * into 目标表名称(自动创建该表) from tablename

insert into 目标表名称(表已经存在) select * from tablename

--//从一个库中将数据导入到另外一个库中

INSERT INTO [a] ([a1], [a2]) SELECT a1,5 FROM OPENROWSET('SQLOLEDB','192.168.2.237';'sa';'sa', 'SELECT a1,a2 FROM [db2].dbo.[a]');

--//查询所有聚集索引上的列信息

select o.name,i.id,i.keyno,i.colid ,c.name from sysindexkeys i left join syscolumns c on i.id = c.id and i.colid = c.colid        left join sysobjects o on i.id = o.id         where i.indid = 1 and        o.xtype = 'U' and o.status > 0        

--//获取所有表信息

exec sp_tables

select * from information_schema.tables

--//获取所有库信息

exec sp_databases

select * from INFORMATION_SCHEMA.SCHEMATA

--//获取列的所有信息

exec sp_columns @table_name = 't1'

--//常用的与元数据有关的系统存储过程有以下一些:

--//系统存储过程 描述

sp_columns 返回指定表或视图的列的详细信息。

sp_databases 返回当前服务器上的所有数据库的基本信息。

sp_fkeys 若参数为带有主键的表,则返回包含指向该表的外键的所有表;若参数为带有外键的表名,则返回所有同过主键/外键关系与该外键相关联的所有表。

sp_pkeys 返回指定表的主键信息。

sp_server_info 返回当前服务器的各种特性及其对应取值。

sp_sproc_columns 返回指定存储过程的的输入、输出参数的信息。

sp_statistics 返回指定的表或索引视图上的所有索引以及统计的信息。

sp_stored_procedures 返回当前数据库的存储过程列表,包含系统存储过程。

sp_tables 返回当前数据库的所有表和视图,包含系统表。

--//常用的与元数据有关的系统函数有以下一些:

--//系统函数 描述

COLUMNPROPERTY 返回有关列或过程参数的信息,如是否允许空值,是否为计算列等。

COL_LENGTH 返回指定数据库的指定属性值,如是否处于只读模式等。

DATABASEPROPERTYEX 返回指定数据库的指定选项或属性的当前设置,如数据库的状态、恢复模型等。

OBJECT_ID 返回指定数据库对象名的标识号

OBJECT_NAME 返回指定数据库对象标识号的对象名。

OBJECTPROPERTY 返回指定数据库对象标识号的有关信息,如是否为表,是否为约束等。

fn_listextendedproperty 返回数据库对象的扩展属性值,如对象描述、格式规则、输入掩码等。

--//信息架构视图 描述

INFORMATION_SCHEMA.CHECK_CONSTRAINTS 返回有关列或过程参数的信息,如是否允许空值,是否为计算列等。

INFORMATION_SCHEMA.COLUMNS 返回当前数据库中当前用户可以访问的所有列及其基本信息。

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 返回当前数据库中定义了约束的所有列及其约束名。

INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE 返回当前数据库中定义了约束的所有表及其约束名。

INFORMATION_SCHEMA.KEY_COLUMN_USAGE 返回当前数据库中作为主键/外键约束的所有列。

INFORMATION_SCHEMA.SCHEMATA 返回当前用户具有权限的所有数据库及其基本信息。

INFORMATION_SCHEMA.TABLES 返回当前用户具有权限的当前数据库中的所有表或者视图及其基本信息。

INFORMATION_SCHEMA.VIEWS 返回当前数据库中的当前用户可以访问的视图及其所有者、定义等信息。

--//系统表 描述

syscolumns 存储每个表和视图中的每一列的信息以及存储过程中的每个参数的信息。

syscomments 存储包含每个视图、规则、默认值、触发器、CHECK 约束、DEFAULT 约束和存储过程的原始 SQL 文本语句。

sysconstraints 存储当前数据库中每一个约束的基本信息。

sysdatabases 存储当前服务器上每一个数据库的基本信息。

sysindexes 存储当前数据库中的每个索引的信息。

sysobjects 存储数据库内的每个对象(约束、默认值、日志、规则、存储过程等)的基本信息。

sysreferences 存储所有包括 FOREIGN KEY 约束的列。

systypes 存储系统提供的每种数据类型和用户定义数据类型的详细信息。

--//修改用户登录关系

sp_change_users_login 'Update_One', 'cb888', 'cb888'

--//修改对象的所有者

sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

--//更改当前数据库的所有者。

sp_changedbowner         [         @loginame         =         ]         'login'

[         ,         [         @map         =         ]         remap_alias_flag         ]                

--//禁用所有约束

alter table tablename nocheck CONSTRAINT all

 

 

 

系统内部结构视图

Sys.indexes

Sys.partitions

Sys.allocation_units

Sys.dm_db_partition_stats

返回当前数据库中每个分区的页和行计数信息。

---------------------------------------------------------------

系统请求,连接,会话的动态视图

Sys.dm_exec_sessions

Sys.dm_exec_connections

Sys.dm_exec_requests

----------------------------------------------------------------

将handler转换成sql的动态管理函数

Sys.dm_exec_sql_text()

-------------------------------------------------------------------------

Sys.dm_os_buffer_description 内存缓冲区统计的函数

Sys.dm_exec_query_stats 提供高速缓冲区内的批处理统计数据

Sys.dm_exec_cached_plans 返回最常编译的执行计划

-----------------------------------------------------------------------------

Sys.dm_exec_tran_locks 返回锁定信息

Sys.dm_os_wating_task 返回等待时间

-----------------------------------------------------------------------------

Sys.dm_io_pending_io_requests

Sys.dm_io_waited_filestats

--------------------------------------------------------------------------

Tempdb

Sys.dm_db_session_space_usage

Sys.dm_db_tssk_space_usage

----------------------------------------------------------------------------

sys.dm_db_index_usage_stats