proc_gl_dd_generation.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

if object_id('dbo.GL_DD_Generation') is not null
drop procedure dbo.GL_DD_Generation
go

create procedure GL_DD_Generation
as
begin

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 (SELECT count(*)
	FROM sysobjects
	WHERE (name in
        	  (SELECT name
	         FROM sysindexes
        	 WHERE (id = a.id) AND (indid in
                	   (SELECT indid
	                  FROM sysindexkeys
        	          WHERE (id = a.id) AND (colid in
                	            (SELECT colid
                        	   FROM syscolumns
	                           WHERE (id = a.id) AND (name = a.name))))))) AND
	       (xtype = 'PK'))>0 then '√' else '' end) 主键,
	b.name 类型,
	a.length 占用字节数,
	COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
	isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
	(case when a.isnullable=1 then '√'else '' end) 允许空,
	isnull(e.text,'') 默认值,
	isnull(g.[value],'') AS 字段说明	

into #temp FROM  syscolumns  a left join systypes b 
on  a.xtype=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 sysproperties g
on a.id=g.id AND a.colid = g.smallid  
order by a.id,a.colorder

insert into GL_Data_Dictionary
(
Object,
Item,
Field,
identify_flag,
Pkey,
Field_Type,
use_space,
length,
prec,
NilAllow,
default_value
)
select 
表名,
字段序号,
字段名,
标识,
主键,
类型,
占用字节数,
长度,
小数位数,
允许空,
默认值
from #temp

select * from GL_Data_Dictionary

drop table #temp
end