SQL SERVER语句 增加字段，查主键，查看表空间大小,查有无主键

 

 SELECT  b.name AS '表名' ,
            CASE WHEN c.NAME IS NULL THEN '无'
                 ELSE '有'
            END AS '主键'
    FROM    sysobjects b
            LEFT JOIN ( ( SELECT    OBJECT_NAME(a.parent_obj) AS name
                          FROM      sysobjects a
                          WHERE     xtype = 'PK'
                        ) ) c ON b.NAME = c.name
    WHERE   b.xtype = 'U'
    ORDER BY c.NAME DESC

 
查询所有字段名：

select NAME from sys.columns where object_id=object_id('A') 

查表明

select name from sys.objects where type='U' and schema_id=1 and is_ms_shipped=0

查询视图名称

select * from  information_schema.views

查条数

SELECT A.NAME ,B.ROWS FROM sysobjects A JOIN sysindexes B ON A.id = B.id WHERE A.xtype = 'U' AND B.indid IN(0,1) ORDER BY B.ROWS DESC

查看表的大小：

exec sp_spaceused '表明'   

 

 

 

 

 

 

 

 

查主键

create table #H(name varchar(50) not null,name_pk varchar(50) null,name_pk1 varchar(50) null )
insert into #H exec sp_helpindex N'text1'    
insert into #H exec sp_helpindex 'text2'    
insert into #H exec sp_helpindex 'text3'    ;

create table H(name varchar(50) not null,name_pk varchar(50) null,name_pk1 varchar(50) null)
insert into H select *   from #H

delete from #H

alter table Lis_B add primary key(sn, sname)

 

 

select table_name,column_name
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where table_name=''
查看表空间大小

create procedure dbo.proc_getsize
as
begin
create table #temp
(
   t_id int primary key identity(1,1),
   t_name sysname,                        --表名
   t_rows int,                            --总行数
   t_reserved varchar(50),                --保留的空间总量
   t_data varchar(50),                    --数据总量
   t_indexsize varchar(50),               --索引总量
   t_unused varchar(50)                   --未使用的空间总量
)

exec SP_MSFOREACHTABLE N'insert into #temp(t_name,t_rows,t_reserved,t_data,t_indexsize,t_unused) exec SP_SPACEUSED ''?'''

select t_id,t_name,t_rows,t_reserved,t_indexsize,t_unused,t_data,
    case when cast(replace(t_data,' KB','') as float)>1000000 then cast(cast(replace(t_data,' KB','') as float)/1000000 as varchar)+' GB' 
        when cast(replace(t_data,' KB','') as float)>1000 then cast(cast(replace(t_data,' KB','') as float)/1000 as varchar)+' MB' 
    else t_data end as datasize
from #temp 
order by cast(replace(t_data,' KB','') as float) desc

drop table #temp

第一步：开启CDC服务

EXECUTE sys.sp_cdc_enable_db

第二步：开启某张表的CDC

 

exec

sys.sp_cdc_enable_table

@source_schema ='dbo',@source_name='Lis_ReportResult',@capture_instance='Lis_ReportResult'

,@supports_net_changes=0,@role_name=null

第三步：修改CDC的保存记录时间点

--显示原有配置

EXEC sp_cdc_help_jobs

GO

--更改数据保留时间为7天

EXECUTE sys.sp_cdc_change_job

    @job_type = N'cleanup',

    @retention=10080

GO

----重启一下作业，以使设置生效

----停用作业

--EXEC sys.sp_cdc_stop_job N'cleanup'

--GO

----启用作业

--EXEC sys.sp_cdc_start_job N'cleanup'

--GO

----再次查看

--EXEC sp_cdc_help_jobs

--GO

 

全量：

CREATE VIEW V_Clinic_Item_ODS_全量 AS

select *

,getdate() as     LastUpdateDtTm

,0 as seqval

,0 as IsDeleted

from [ECISPlatform_4.1].dbo.Clinic_Item as a with(nolock)

增量：

CREATE view V_MH_AccordingRecord_ODS_CDC

as

SELECT [AID]

      ,[TID]

      ,[PVID]

      ,[RecordDT]

      ,[SystemID]

      ,[SymptomID]

      ,[AccordingItemID]

      ,[Operator]

,c.tran_begin_time as LastUpdateDtT