SQLServer常用操作系列一---------表常用操作

一、自增

-- 查詢種子值：
dbcc checkident(表名,noreseed)

--重設(shè)置種子值為2：
dbcc checkident(表名，reseed,2)

二、重命名系列：

sp_renamedb
sp_rename employees,Empinfo重命名表名。
sp_rename 'Employees.Wage','salary','column'  將表employees中的列wage,重命名為salary。

三、修改表結(jié)構(gòu)

--向表中添加列：
alter table 表名 add 列名 類型 列屬性
alter table employees add tele varchar(50) null

--修改列屬性：
alter table 表名 alter column 列名 新數(shù)據(jù)類型和長(zhǎng)度 新列屬性
如：
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獲取表中的外鍵約束：

--創(chuàng)建唯一索引
create unique nonclustered index 

--修改索引：
alter index {索引名|all} on <xxx.> {rebuild|disable|reorganize}

--從系統(tǒng)視圖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

各種重組索引的方式
 --不指定參數(shù)重組索引：  
  ALTER INDEX [idx_refno] ON [ordDemo]  REORGANIZE  
  GO    
--重組表中所有索引：  
  ALTER INDEX ALL ON [ordDemo]   REORGANIZE  
  GO  

--使用DBCC INDEXDEFRAG重建表上所有索引：  
DBCC INDEXDEFRAG('AdventureWorks','ordDemo')  
GO  
  
--使用DBCC INDEXDEFRAG重組表上一個(gè)索引：  
 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函數(shù)檢測(cè)指定索引的碎片情況：
use hrsystem
select * from sys.dm_db_index_physical_stats(DB_ID(),object_id(N'Credit.consume'),NULL,NULL,NULL)

--檢測(cè)數(shù)據(jù)庫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

--查看表索引信息和統(tǒng)計(jì)信息
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  

--查看某一對(duì)象的統(tǒng)計(jì)信息
dbcc show_statistics(ac_application表名,p_application統(tǒng)計(jì)信息名)

五、統(tǒng)計(jì)信息

--使用sys.stats查看統(tǒng)計(jì)信息：
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

-查看對(duì)象統(tǒng)計(jì)信息
select * from sales.SalesOrderDetail where UnitPrice = 35
dbcc show_statistics('sales.salesorderdetail',unitprice)
go

--查看對(duì)象統(tǒng)計(jì)信息更新情況
--使用sys.stats_columns查看統(tǒng)計(jì)信息中列的信息
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'

--查詢統(tǒng)計(jì)信息時(shí)間
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')

--更新統(tǒng)計(jì)信息
exec sp_updatestats
 
--刪除統(tǒng)計(jì)信息
drop statistics <表名>.<統(tǒng)計(jì)信息名>|

--使用dbcc show_statistics命令查看統(tǒng)計(jì)信息的明細(xì)信息
dbcc show_statistics('Employees',IX_Wage)

--使用sp_autostats存儲(chǔ)過程查看索引自動(dòng)創(chuàng)建的統(tǒng)計(jì)信息：
use hrsystem
go
exec sp_autostats 'Employees'
gp

--創(chuàng)建統(tǒng)計(jì)信息
alter database hrsystem set auto_create_statistics off
create statistics  xxx on employees(idcard)
--設(shè)置統(tǒng)計(jì)信息隨機(jī)抽樣為5%
create statistics ix_title on employees(title)
    with sample 5 percent
go

--使用sp_createstats存儲(chǔ)過程創(chuàng)建統(tǒng)計(jì)信息(可以為當(dāng)前數(shù)據(jù)庫所有表的合格列和內(nèi)部表創(chuàng)建單列的統(tǒng)計(jì)信息)
exec sp_createstats;

-修改統(tǒng)計(jì)信息：
--更新指定表的所有統(tǒng)計(jì)信息
use hrsystem
update statistics employees
go
--更新指定表的單個(gè)索引的統(tǒng)計(jì)信息：
use hrsystem
go
update statistics employees pk_employees
--對(duì)全表進(jìn)行掃描，更新統(tǒng)計(jì)信息
use hrsystem
go
update statistics employees(ix_wage) with fullscan
go

--刪除統(tǒng)計(jì)信息

其它統(tǒng)計(jì)信息相關(guān)腳本:

六、其它對(duì)象

--查詢系統(tǒng)視圖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'

--從系統(tǒng)表sys.objects中獲取所有數(shù)據(jù)庫對(duì)象的信息：
select * from sys.objects where type='pk'

--將sys.indexes與sys.objects相關(guān)聯(lián)
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 

--進(jìn)一步根據(jù)hobt_id查詢某一partition信息
select * from sys.system_internals_allocation_units where container_id = 72057594050641920 

--查詢進(jìn)一步信息
select * from sys.databases;
select * from sysdatabases;
select OBJECT_DEFINITION (object_id('sys.tables'));
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容