一些实用的带判断SQL(自己整理)

--符合条件的所有列数据拼接

SELECT 

main_hobby=STUFF((SELECT ','+EmpNo+':'+EmpName 

FROM 

All_Employee WHERE PartID=t.sid FOR XML PATH('')),1,1,'')

FROM Part t

GROUP BY t.sid

--判断字段是否有默认约束

if not exists(

select d.name

from syscolumns a

join sysobjects b on a.id=b.id

join syscomments c on a.cdefault=c.id

join sysobjects d on c.id=d.id

where b.name= '表名'

and a.name= '字段名' 

)

BEGIN

alter table 表名 add default(默认值) for 字段名 

EXEC SP_EXECUTESQL N'UPDATE 表名 SET 字段名 = 默认值 WHERE 字段名 is Null'

END

--查列类型

select a.name 表名,b.name 字段名,c.name 字段类型,c.length 字段长度 from sysobjects a,syscolumns b,systypes c where a.id=b.id

and a.name='表名' and a.xtype='U'-- and b.name ='Days'

and b.xtype=c.xtype

--查询字段是否允许为空(不允许就显示1)

SELECT  

        1

    FROM sys.columns C  

    WHERE C.[object_id] = OBJECT_ID('表名') and C.name ='字段' and C.is_nullable <>1

--查询表的字段的各属性

declare @table_name   varchar(100)-- 表名  

 set @table_name='表名'

select 类别,表名or字段名,描述,字段类型,是否自增,允许为NULL,默认值 from   

    (  

    SELECT '表名' 类别,-1 column_id,tbs.name 表名or字段名,ds.value 描述,'' 字段类型,'' 是否自增,'' 允许为NULL,'' 默认值,1 rn  

    FROM sys.extended_properties ds    

    LEFT JOIN sysobjects tbs ON ds.major_id=tbs.id    

    WHERE  ds.minor_id=0 and tbs.name=@table_name  

    union  

    SELECT  

        @table_name 类别  

        ,c.column_id  

        ,C.name 表名or字段名  

        ,s.value 描述  

        ,字段类型 = T.name + CASE T.user_type_id WHEN 41 THEN '('+CAST(C.scale AS VARCHAR) +')'     -- time  

            WHEN 42 THEN '('+CAST(C.scale AS VARCHAR) +')'                                      -- datetime2  

            WHEN 43 THEN '('+CAST(C.scale AS VARCHAR) +')'                                      -- datetimeoffset  

            WHEN 106 THEN '('+CAST(C.precision  AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')'  -- decimal  

            WHEN 108 THEN '('+CAST(C.precision  AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')'  -- numeric  

            WHEN 165 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'       -- varbinary  

            WHEN 167 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'       -- varchar  

            WHEN 173 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'       -- binary  

            WHEN 175 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'       -- char  

            WHEN 231 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1)/2 AS VARCHAR),'MAX') +')'     -- nvarchar(该字段校检根据实际情况)  

            WHEN 239 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'       -- nchar  

            ELSE ''  

            END  

        ,case when C.is_identity=1 then '是' else '' end 是否自增

        --cast(C.is_identity as varchar(10)) 是否自增  

        ,case when C.is_nullable=1 then '是'else '' end 允许为NULL  

        ,默认值 = ISNULL(STUFF(LEFT(D.definition,LEN(D.definition)-1),1,1,''),'')   

        ,3 rn  

    FROM sys.columns C  

    INNER JOIN sys.types T ON C.user_type_id = T.user_type_id   

    LEFT JOIN sys.default_constraints D ON D.[object_id] =C.default_object_id AND D.parent_object_id = C.[object_id] AND D.parent_column_id = C.column_id   

    left join (select major_id,minor_id,value from sys.extended_properties) s on s.major_id = c.object_id and s.minor_id = c.column_id  

    WHERE C.[object_id] = OBJECT_ID(@table_name)  

    ) s   

    order by column_id,rn  

--查表(视图)类型

SELECT

  C.colorder,

    T.NAME AS ColumnsType,

    C.NAME AS ColumnsName,

    CASE

WHEN (

    T.NAME = 'varchar'

    OR T.NAME = 'nvarchar'

)

AND C.length < 0 THEN

    T.NAME + '(4000)'

WHEN T.NAME = 'varchar'

OR T.NAME = 'nvarchar' THEN

    T.NAME + '(' + CAST (C.length AS VARCHAR) + ')'

ELSE

    T.NAME

END type

FROM

    SysObjects AS o,

    SysColumns AS C,

    SysTypes AS T

WHERE

    o.TYPE IN ('u', 'v')

AND o.ID = C.ID

AND C.xtype = T.xtype

AND T.NAME != 'sysname'

AND UPPER (o.NAME) = UPPER ('表/视图名')

--AND C.length < 2000 --判断类型长度(一般只判断字符串)

 order by C.colorder asc

--实现一个表中的数据更新另一个表中的数据

--第一步：首先查询你想要的语句到一个临时表中。可以查看一下是否插入成功

select ROW_NUMBER() OVER(ORDER BY ID)as RowID,* into #tabl1 from V_QM_335 where id>=276     --获取你要的数据插入到一个临时表中

select * from #tabl1

--第二步:

update A SET A.cj = b.cj FROM A ,B WHERE A.Name = B.Name

-- 查表的字段是否没有默认值

if NOT exists(SELECT  1 FROM    sys.tables t INNER JOIN sys.columns c ON t.object_id = c.object_id

INNER JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id

WHERE   t.name = '你的表名称' AND c.name = '你的列名称')

--修改字段类型

IF EXISTS(select a.name 表名,b.name 字段名,c.name 字段类型,c.length 字段长度 from sysobjects a,syscolumns b,systypes c where a.id=b.id

and a.name='表名' and a.xtype='U' and b.name ='字段名' and c.name = '当前或指定字段类型'

and b.xtype=c.xtype)

alter table Employee alter column zkempname nvarchar(max)--改成另一个类型

GO--GO

--nvarchar类型增长

IF EXISTS(

SELECT

  C.colorder,

    T.NAME AS ColumnsType,

    C.NAME AS ColumnsName,

    CASE

WHEN (

    T.NAME = 'nvarchar'

    OR T.NAME = 'nvarchar'

)

AND C.length < 0 THEN

    T.NAME + '(4000)'

WHEN T.NAME = 'nvarchar'

OR T.NAME = 'nvarchar' THEN

    T.NAME + '(' + CAST (C.length AS nvarchar) + ')'

ELSE

    T.NAME

END type

FROM

    SysObjects AS o,

    SysColumns AS C,

    SysTypes AS T

WHERE

    o.TYPE IN ('u', 'v')

AND o.ID = C.ID

AND C.xtype = T.xtype

AND T.NAME != 'sysname'

AND UPPER (o.NAME) = UPPER ('AddWorkTimeReq_Rest')

AND C.NAME = 'addwhy'

AND C.length < 2000

)

alter table AddWorkTimeReq_Rest alter Column addwhy nvarchar(2000)

GO--GO

--加表注释

--表注释  @value为注释内容  @leve12name为注释表名

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'错误信息表' , @level0type=N'SCHEMA',

@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_ErrorLogTable_tb'

--列注释 @value为注释内容   @leve12nane为注释列名

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号' ,

@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_ErrorLogTable_tb',

@level2type=N'COLUMN',@level2name=N'ELTID';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'错误发生日期' ,

@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_ErrorLogTable_tb',

@level2type=N'COLUMN',@level2name=N'ELTTime';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'错误发生地址' ,

@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_ErrorLogTable_tb',

@level2type=N'COLUMN',@level2name=N'ELTAddress';

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'错误发生信息' ,

@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_ErrorLogTable_tb',

@level2type=N'COLUMN',@level2name=N'ELTMessage';

------sqlserver 查询某个表的列名称、说明、备注、类型等

SELECT 

    表名       = case when a.colorder=1 then d.name else '' end,

    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,

    字段序号   = a.colorder,

    字段名     = a.name,

    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,

    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (

                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,

    类型       = b.name,

    占用字节数 = a.length,

    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),

    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),

    允许空     = case when a.isnullable=1 then '√'else '' end,

    默认值     = isnull(e.text,''),

    字段说明   = isnull(g.[value],'')

FROM 

    syscolumns a

left join 

    systypes b 

on 

    a.xusertype=b.xusertype

inner join 

    sysobjects d 

on 

    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'

left join 

    syscomments e 

on 

    a.cdefault=e.id

left join 

sys.extended_properties   g 

on 

    a.id=G.major_id and a.colid=g.minor_id  

left join

sys.extended_properties f

on 

    d.id=f.major_id and f.minor_id=0

where 

    d.name='Sys_User'    --如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息

order by 

    a.id,a.colorder

--表注释修改

if (select count(0) from sys.extended_properties where major_id=object_Id('表名') and minor_id=0)>0

    begin

        EXEC sys.sp_updateextendedproperty @name=N'MS_Description',@value=N'备注1',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'表名'

    end

else

    begin

        EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'备注2',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'表名'

    end

GO--GO

--新增表注释

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'备注',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'表名';

--或

EXEC sp_addextendedproperty 'MS_Description','备注','SCHEMA','dbo','TABLE','表名';

--修改表注释

EXEC sys.sp_updateextendedproperty @name=N'MS_Description',@value=N'备注',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'表名';

--或

EXEC sp_updateextendedproperty 'MS_Description','备注','SCHEMA','dbo','TABLE','表名';

--删除表注释，EXEC同EXECUTE

EXEC sys.sp_dropextendedproperty @name=N'MS_Description',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'表名';

--或

EXECUTE sp_dropextendedproperty 'MS_Description','SCHEMA','dbo','TABLE','表名';

--字段注释(扩展属性)

EXEC sp_addextendedproperty 

@name = N'MS_Description',

@value = N'This is a column description on [name](2).',

@level0type = N'SCHEMA', @level0name = N'dbo',

@level1type = N'TABLE', @level1name = N'T8',

@level2type = N'COLUMN', @level2name = N'name'

GO

--指定表的指定列有注释才会执行

IF EXISTS(

SELECT 

    a.name,

    字段说明   = isnull(g.[value],'')

FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join  

  sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join  syscomments e on a.cdefault=e.id left join sys.extended_properties g  

on  a.id=G.major_id and a.colid=g.minor_id left join sys.extended_properties f

on d.id=f.major_id and f.minor_id=0

where  

  g.[value] is not null

  and

  g.[value] <>''

  and 

  d.name='表名'

  and 

  a.name = '列名'  

)

Begin

End

GO--GO

--添加字段

IF COL_LENGTH('表名', '字段') IS NULL 

Begin

 ALTER TABLE 表名 ADD 字段 类型

 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'中文注释' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'字段名'

END

GO--GO

--判断默认值是否指定值

IF EXISTS (SELECT 1

FROM sys.tables ST

INNER JOIN sys.syscolumns SC ON ST.[object_id] = SC.[id]

INNER JOIN sys.default_constraints SD ON ST.[object_id] = SD.[parent_object_id] AND SC.colid = SD.parent_column_id

where ST.[name]='表名' and SC.[name]= '列名' and SD.definition='(原默认值)'

)

Begin

End

--修改默认值

--注意原默认值内容

IF EXISTS (SELECT 1

FROM sys.tables ST

INNER JOIN sys.syscolumns SC ON ST.[object_id] = SC.[id]

INNER JOIN sys.default_constraints SD ON ST.[object_id] = SD.[parent_object_id] AND SC.colid = SD.parent_column_id

where ST.[name]='表名' and SC.[name]= '字段名' and SD.definition='原默认值内容'

)

Begin

--删除默认约束

declare @tablename varchar(50)

declare @fieldname varchar(50)

declare @dpname varchar(50)

declare @sql varchar(300)

set @tablename = '表名'

set @fieldname = '字段名'

SELECT top 1 @dpname =SD.[name] FROM sys.tables ST INNER JOIN sys.syscolumns SC ON ST.[object_id] = SC.[id] INNER JOIN sys.default_constraints SD ON ST.[object_id] = SD.[parent_object_id] AND SC.colid = SD.parent_column_id where ST.[name]=@tablename and SC.[name]= @fieldname and SD.definition='原默认值内容'

set @sql=''

select @sql = @sql + 'alter table ['+ @tablename + '] drop constraint [' + @dpname + ']'

exec(@sql)

End

GO--GO

if not exists(

select d.name

from syscolumns a

join sysobjects b on a.id=b.id

join syscomments c on a.cdefault=c.id

join sysobjects d on c.id=d.id

where b.name= '表名'

and a.name= '字段名' 

)

BEGIN

--添加新约束

alter table 表名add default(新默认值) for 字段名

--下面看情况要不要

EXEC SP_EXECUTESQL N'UPDATE 表名 SET EventType = 新默认值 WHERE EventType = 旧默认值'

END

GO--GO

例子:

IF EXISTS (SELECT 1

FROM sys.tables ST

INNER JOIN sys.syscolumns SC ON ST.[object_id] = SC.[id]

INNER JOIN sys.default_constraints SD ON ST.[object_id] = SD.[parent_object_id] AND SC.colid = SD.parent_column_id

where ST.[name]='AssetUseToEndEntity_MaintenanceAndRepair' and SC.[name]= 'EventType' and SD.definition='(''维修'')'

)

Begin

--删除默认约束

declare @tablename varchar(50)

declare @fieldname varchar(50)

declare @dpname varchar(50)

declare @sql varchar(300)

set @tablename = 'AssetUseToEndEntity_MaintenanceAndRepair'

set @fieldname = 'EventType'

SELECT top 1 @dpname =SD.[name] FROM sys.tables ST INNER JOIN sys.syscolumns SC ON ST.[object_id] = SC.[id] INNER JOIN sys.default_constraints SD ON ST.[object_id] = SD.[parent_object_id] AND SC.colid = SD.parent_column_id where ST.[name]=@tablename and SC.[name]= @fieldname and SD.definition='(''维修'')'

set @sql=''

select @sql = @sql + 'alter table ['+ @tablename + '] drop constraint [' + @dpname + ']'

exec(@sql)

End

GO--GO

if not exists(

select d.name

from syscolumns a

join sysobjects b on a.id=b.id

join syscomments c on a.cdefault=c.id

join sysobjects d on c.id=d.id

where b.name= 'AssetUseToEndEntity_MaintenanceAndRepair'

and a.name= 'EventType' 

)

BEGIN

--添加新约束

alter table AssetUseToEndEntity_MaintenanceAndRepair add default('保修') for EventType 

EXEC SP_EXECUTESQL N'UPDATE AssetUseToEndEntity_MaintenanceAndRepair SET EventType = ''保修'' WHERE EventType = ''维修'''

END

GO--GO

--要以存储过程结果为对象查询例子

--增加索引

declare @t table (col1 varchar(100),col2 varchar(100),col3 varchar(100))

insert into @t exec sp_helpindex '表名'

IF NOT EXISTS(select 1 from @t where col3 = '索引组合')--如果多个列,要以, (,空格.空格不能少)分开,而且要按先后顺序

CREATE INDEX IK_EmpNosid ON EmployeeDayCardData(EmpNosid)

GO--GO

--例子:

declare @t table (col1 varchar(100),col2 varchar(100),col3 varchar(100))

insert into @t exec sp_helpindex 'EmployeeDayCardData'

IF NOT EXISTS(select * from @t where col3 = 'YYMMDD, EmpNosid')

CREATE INDEX IK_EmpNosid ON EmployeeDayCardData(EmpNosid)

GO--GO