字段说明= isnull(G.[value],''), 标识= Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1Then'√'Else''End, 主键= Case When exists(SELECT1FROM sysobjects Where xtype='PK'and parent_obj=A.id and namein( SELECT name FROM sysindexes WHERE indidin( SELECT indid FROM sysindexkeys WH...
表名=d.name,--case when a.colorder=1 then d.name 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 name in ( SELECT name FRO...
如果是sql2005以后的话应该这样写select表名casewhenacolorder1dnameelseend表说明casewhenacolorder1isnullfvalue??elseend字段序号acolorder字段名aname标识casewhencolumnpropertyaidaname?isidentity?1end主键casewhenexistsselectfromsysobjectswherextype?pk?selectnamefromsysindexeswhereindidselectindidfromsysindexkeyswhere...
where not exists(select 1 from #indexes1 where tbname=t.tbname and indexcol=t.indexcol) --index is different on isclustered insert #difference(desc_difference,objecttype,objectname) select case a.isclustered when 1 then 'Clustered' else 'Not clustered' end +'--'+@db1+', ' +case b.i...
go /*4.显示当前数据库所有表的详细信息*/ use 数据库名 go SELECT 表名=case when a.colorder=1 then else '' end, 字段序号=a.colorder, 字段名=, 标识=case when COLUMNPROPERTY(,,'IsIdentity')=1 then '√'else '' end, 主键=case when exists ...
exists()适合B表比A表数据大的情况 当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用。 七、把数据从一个表插入到另外一个表中 insert into [Table1](proCode) (select proCode from [Table2] group by proCode); 1. 八、union ...
ISNULL(ep.[value], '') AS列说明 ,t.name AS数据类型 ,col.length AS长度 ,ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS⼩数位数 ,CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') =1THEN'√'ELSE''END AS标识 ,CASE WHEN EXISTS ( SELECT1 FROM dbo.sysindexes si...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_WorkDay]GO --计算两个⽇期相差的⼯作天数 CREATE FUNCTION f_WorkDay(@dt_begin datetime, --计算的开始⽇期 @dt_end datetime ...
IF EXISTS ( SELECT TOP 1 1 FROM sys.sysprocesses WHERE program_name = 'SQLAgent - Generic Refresher' ) SELECT 'Running' ELSE SELECT 'Not Running' 25. 查看活动线程执行的sql语句,并生成批量杀掉的语句 select 'KILL '+CAST(a.spid AS NVARCHAR(100)) AS KillCmd,REPLACE(hostname,' ','') as...
② 搜索表达式:将返回结果为TRUE的第一个WHEN逻辑表达式所关联的THEN子句中指定的值。如果没有任何WHEN表达式结果为TRUE,CASE表达式则返回ELSE子句中出现的值。(如果没有指定ELSE,则默认返回NULL); 代码语言:javascript 代码运行次数:0 运行 AI代码解释 SELECTorderid,custid,val,valuecategory=(CASEWHENval<1000.00THEN...