SQLServer常用操作系列一---------表常用操作
2020-06-10 本文已影响0人
e652d1fb12eb
一、自增
-- 查询种子值：
dbcc checkident(表名,noreseed)
--重设置种子值为2：
dbcc checkident(表名，reseed,2)
二、重命名系列：
sp_renamedb
sp_rename employees,Empinfo重命名表名。
sp_rename 'Employees.Wage','salary','column' 将表employees中的列wage,重命名为salary。
三、修改表结构
--向表中添加列：
alter table 表名 add 列名 类型 列属性
alter table employees add tele varchar(50) null
--修改列属性：
alter table 表名 alter column 列名 新数据类型和长度 新列属性
如：
alter table employees alter column tele char(30) null
--删除表中的列
alter table employees drop column tele
四、索引与约束
--修改主键约束：
alter table xxx add constraint pk_xxx primary key nonclustered (xxx)
alter table xxx drop constraint pk_xxx
alter table constraint xxx unique (xxx)
alter table xxx add constraint xxx check(sex='男' or sex='女')
alter table Employees add constraint de_title default '职员' for title
--删除约束
alter table xxx drop constraint ck_sex
go
--从sys.key_constraints获取约束信息
select * from sys.key_constrains
--从information_schema.check_constraints获取检查约束信息
select * from information_schema.check_constraints
--从sys.foreign_keys获取表中的外键约束：
--创建唯一索引
create unique nonclustered index
--修改索引：
alter index {索引名|all} on <xxx.> {rebuild|disable|reorganize}
--从系统视图sys.indexes查询索引信息
use hrsystem
go
select * from sys.indexes
go
sp_helpindex 'Tablename';
go
--查看索引
select * from sys.index_columns
go
--从sys.dm_db_index_usage_stats中查询索引操作的信息：
use xxx
select * from sys.dm_db_index_usage_stats
各种重组索引的方式
--不指定参数重组索引：
ALTER INDEX [idx_refno] ON [ordDemo] REORGANIZE
GO
--重组表中所有索引：
ALTER INDEX ALL ON [ordDemo] REORGANIZE
GO
--使用DBCC INDEXDEFRAG重建表上所有索引：
DBCC INDEXDEFRAG('AdventureWorks','ordDemo')
GO
--使用DBCC INDEXDEFRAG重组表上一个索引：
DBCC INDEXDEFRAG('AdventureWorks','ordDemo','idx_refno')
GO
--重新生成索引：
alter index ix_wage on employees rebuild WITH (ONLINE = ON);
DBCC DBREINDEX
--禁用索引：
alter index ix_wage on employees disable;
使用sys.dm_db_index_physical_stats函数检测指定索引的碎片情况：
use hrsystem
select * from sys.dm_db_index_physical_stats(DB_ID(),object_id(N'Credit.consume'),NULL,NULL,NULL)
--检测数据库hrsystem中表employees的所有索引的碎片情况：
use hrsystem
go
select a.index_id,name,avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'Credit.Consume'),
NULL,NULL,NULL) as a
join sys.indexes as b On a.object_id=b.object_id and a.index_id=b.index_id;
go
DBCC INDEXDEFRAG
--查看表索引信息和统计信息
DECLARE @tblnvarchar(265)
SELECT @tbl = '表名'
SELECT o.name,i.index_id, i.name, i.type_desc,
substring(ikey.cols, 3, len(ikey.cols))AS key_cols,
substring(inc.cols, 3, len(inc.cols)) ASincluded_cols,
stats_date(o.object_id, i.index_id) ASstats_date,
i.filter_definition
FROM sys.objects o
JOIN sys.indexes i ON i.object_id = o.object_id
CROSS APPLY (SELECT ', ' + c.name +
CASE ic.is_descending_key
WHEN 1 THEN ' DESC'
ELSE ''
END
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id
ANDic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')) AS ikey(cols)
OUTER APPLY (SELECT ', ' + c.name
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id
ANDic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR XML PATH('')) AS inc(cols)
WHERE o.name = @tbl
AND i.type IN (1, 2)
ORDER BY o.name, i.index_id
--查看某一对象的统计信息
dbcc show_statistics(ac_application表名,p_application统计信息名)
五、统计信息
--使用sys.stats查看统计信息：
select o.name,s.name,auto_created,user_created from sys.stats s inner join sysobjects o
on s.object_id=o.id
where o.name='Employees'
go
-查看对象统计信息
select * from sales.SalesOrderDetail where UnitPrice = 35
dbcc show_statistics('sales.salesorderdetail',unitprice)
go
--查看对象统计信息更新情况
--使用sys.stats_columns查看统计信息中列的信息
select * from sys.stats
go
select * from sys.stats_columns
go
select object_id,stats_id
from sys.stats
where OBJECT_ID = OBJECT_ID('sales.salesorderdetail')
and name = 'Pk_salesorderdetail_slaesorderid_salesorderdetailid'
--查询统计信息时间
select stats_date(1154103142,1)
select name,auto_created,STATS_DATE(object_id,stats_id) as update_date
from sys.stats
where object_id = OBject_id('sales.salesorderdetail')
--更新统计信息
exec sp_updatestats
--删除统计信息
drop statistics <表名>.<统计信息名>|
--使用dbcc show_statistics命令查看统计信息的明细信息
dbcc show_statistics('Employees',IX_Wage)
--使用sp_autostats存储过程查看索引自动创建的统计信息：
use hrsystem
go
exec sp_autostats 'Employees'
gp
--创建统计信息
alter database hrsystem set auto_create_statistics off
create statistics xxx on employees(idcard)
--设置统计信息随机抽样为5%
create statistics ix_title on employees(title)
with sample 5 percent
go
--使用sp_createstats存储过程创建统计信息(可以为当前数据库所有表的合格列和内部表创建单列的统计信息)
exec sp_createstats;
-修改统计信息：
--更新指定表的所有统计信息
use hrsystem
update statistics employees
go
--更新指定表的单个索引的统计信息：
use hrsystem
go
update statistics employees pk_employees
--对全表进行扫描，更新统计信息
use hrsystem
go
update statistics employees(ix_wage) with fullscan
go
--删除统计信息
其它统计信息相关脚本:
六、其它对象
--查询系统视图sys.objects和sys.columns中的id值。
select * from sys.columns where object_id=2105058535 and column_id=7
use adventureworks2014
select * from sys.objects where name = 'Employee'
--从系统表sys.objects中获取所有数据库对象的信息：
select * from sys.objects where type='pk'
--将sys.indexes与sys.objects相关联
select o.name as 表名,i.name as 索引名,i.type_desc as 类型描述,
is_primary_key as 主键约束,is_unique_constraint as 唯一约束,is_disabled as 禁用 from sys.objects o inner join sys.indexes i on i.object_id=o.object_id
use master;
select name from sys.objects where type_desc='system_table';
select * from sys.objects;
select * from sys.objects where name='employee' --得到该表的objectID
select * from sys.partitions where object_id = '1237579447'
--得到该表的所有partition
select * from sys.allocation_units where container_id = 72057594050641920
--进一步根据hobt_id查询某一partition信息
select * from sys.system_internals_allocation_units where container_id = 72057594050641920
--查询进一步信息
select * from sys.databases;
select * from sysdatabases;
select OBJECT_DEFINITION (object_id('sys.tables'));
