字段说明= 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...
字段名=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 FROM sysindexes WHERE indid in ( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid...
1SELECT 2表名= CASE a.colorder WHEN 1 THEN c.name ELSE '' END, 3序= a.colorder, 4字段名 = a.name, 5标识= CASE COLUMNPROPERTY(a.id,a.name,'IsIdentity') WHEN 1 THEN '√' ELSE '' END, 6主键= CASE 7WHEN EXISTS ( 8SELECT * 9FROM sysobjects 10WHERE xtype='PK' AND name IN ...
当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用。 七、把数据从一个表插入到另外一个表中 insert into [Table1](proCode) (select proCode from [Table2] group by proCode); 1. 八、union select deptno, count(1) 总人数, count(case when job ='SALESMAN' then '1' end) ...
如果是sql2005以后的话应该这样写select表名casewhenacolorder1dnameelseend表说明casewhenacolorder1isnullfvalue??elseend字段序号acolorder字段名aname标识casewhencolumnpropertyaidaname?isidentity?1end主键casewhenexistsselectfromsysobjectswherextype?pk?selectnamefromsysindexeswhereindidselectindidfromsysindexkeyswhere...
use 数据库名 go SELECT 表名=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 ...
WHERE EXISTS (SELECT 1 FROM TITLES WHERE TYPE = ’BUSINESS’ AND PUB_ID= PUBLISHERS.PUB_ID) D、不要用COUNT()的子查询判断是否存在记录,最好用LEFT JOIN或者EXISTS,比如有人写这样的语句: SELECT JOB_DESC FROM JOBS WHERE (SELECT COUNT() FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0 ...
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 ...
[主键]=case when exists(select 1 from sys.objects where parent_object_id=a.object_id and type=N'PK' and name in (select Name from sys.indexes where index_id in (select indid from sysindexkeys where ID=a.object_id and colid=a.column_id))) ...
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...