fetchsql.sql

客户关系管理系统ASP.NET+VB.NET编程完整程序！

set nocount on

declare @database table 
	(	[id] int identity(1,1), 
		[databasename] varchar(200), 
		[databasetoken] varchar(100)
	)

declare	@table table 
	(	[id] int identity(1,1), 
		[fkdatabaseid] int, 
		[owner] varchar(200),
		[name] varchar(200),
		[allownew] varchar(5),
		[islookuptable] varchar(5),
		[generatecodefor] varchar(5),
		[accessorname] varchar(100),
		[generatesamplecode] varchar(5)
	) 

declare	@datatype table
	(	[id] int identity(1,1),
		[sqltype] varchar(50),
		[nettype] varchar(50),
		[xmltype] varchar(50),
		[netsqltype] varchar(50)
	)


declare	@column table 
	(	[id] int identity(1,1),
		[fktableid] int, 
		[name] varchar(200), 		
		[fkdatatypeid] int, 
		[length] int,
		[primarykey] varchar(5),
		[setaccessor] varchar(50),
		[getaccessor] varchar(50),
		[requiredforinstantiation] varchar(50),
		[accessorname] varchar(100),
		[nullable] varchar(5),
		[isidentity] varchar(5),
		[isreadonly] varchar(5)
	) 

declare	@relation table 
	(	[id] int identity(1,1),
		[fkparenttableid] int, 
		[fkchildtableid] int, 
		[foreignkeycolumnname] varchar(200), 
		[parentconstraintname] varchar(200),
		[childconstraintname] varchar(200),
		[relationshiptype] varchar(5), 
		[childaccessorname] varchar(200), 
		[parentaccessorname] varchar(200), 
		[childsetaccessor] varchar(50),
		[childgetaccessor] varchar(50),
		[parentsetaccessor] varchar(50),
		[parentgetaccessor] varchar(50),
		[ismandatory] varchar(5), 
		[disassociatechild] varchar(5),
		[cascadeupdate] varchar(5), 
		[cascadedelete] varchar(5),
		[enforceconstraint] varchar(5),
		[nullablefkey] varchar(5)
	)

declare	@relationcolumn table
	(
		[id] int identity(1,1),
		[fkrelationid] int,
		[parentcolumnname] varchar(200),
		[childcolumnname] varchar(200),
		[ordinalposition] int,
		[nullablefkey] varchar(5),
		[childcolumnisprimarykey] varchar(5)
	)

declare	@procedure table
	(	[id] int identity(1,1),
		[fkdatabaseid] int,
		[owner] varchar(200),
		[name] varchar(200)
	)

declare	@param	table
	(	[id] int identity(1,1),
		[fkprocedureid] int,
		[name] varchar(200),
		[fkdatatypeid] int,
		[length] int,
		[isoutputparam] varchar(5)
	)


DECLARE @tmpRelations table
	(
		[Constraint] varchar(800),
		[PrimaryKeyName] varchar(800),
		[ForeignTable] varchar(800),
		[ForeignColumn] varchar(800),
		[PrimaryTable] varchar(800),
		[PrimaryColumn] varchar(800),
		[UpdateRule] varchar(800),
		[DeleteRule] varchar(800),
		[Ordinal] int
	)


/* populate the data types table, for mapping sql to .net to xml types */
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('bigint', 'System.Int64', 'xsd:long', 'System.Data.SqlTypes.SqlInt64')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('binary', 'System.Byte[]', 'xsd:base64Binary', 'System.Data.SqlTypes.SqlBinary')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('bit', 'System.Boolean', 'xsd:boolean', 'System.Data.SqlTypes.SqlBoolean')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('char', 'System.String', 'xsd:string', 'System.String')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('datetime', 'System.DateTime', 'xsd:dateTime', 'System.Data.SqlTypes.SqlDateTime')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('decimal', 'System.Decimal', 'xsd:decimal', 'System.Data.SqlTypes.SqlDecimal')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('float', 'System.Double', 'xsd:float', 'System.Data.SqlTypes.SqlDouble')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('image', 'System.Byte[]', 'xsd:base64Binary', 'System.Data.SqlTypes.SqlBinary')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('int', 'System.Int32', 'xsd:int', 'System.Data.SqlTypes.SqlInt32')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('money', 'System.Decimal', 'xsd:decimal', 'System.Data.SqlTypes.SqlMoney')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('nchar', 'System.String', 'xsd:string', 'System.String')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('ntext', 'System.String', 'xsd:string', 'System.String')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('numeric', 'System.Decimal', 'xsd:decimal', 'System.Data.SqlTypes.SqlDecimal')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('nvarchar', 'System.String', 'xsd:string', 'System.String')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('real', 'System.Single', 'xsd:float', 'System.Data.SqlTypes.SqlSingle')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('smalldatetime', 'System.DateTime', 'xsd:dateTime', 'System.Data.SqlTypes.SqlDateTime')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('smallint', 'System.Int16', 'xsd:int', 'System.Data.SqlTypes.SqlInt16')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('smallmoney', 'System.Decimal', 'xsd:decimal', 'System.Data.SqlTypes.SqlMoney')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('text', 'System.String', 'xsd:string', 'System.String')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('timestamp', 'System.Byte[]', 'xsd:base64Binary', 'System.Data.SqlTypes.SqlBinary')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('tinyint', 'System.Byte', 'xsd:int', 'System.Data.SqlTypes.SqlByte')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('uniqueidentifier', 'System.Guid', 'xsd:string', 'System.Guid')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('varbinary', 'System.Byte[]', 'xsd:base64Binary', 'System.Data.SqlTypes.SqlBinary')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('varchar', 'System.String', 'xsd:string', 'System.String')
insert	@datatype(  sqltype, nettype, xmltype, netsqltype) values ('sql_variant', 'System.Object', 'xsd:string', 'System.Object')


/* create an entry in the database table for this db */
insert  @database (databasename, databasetoken) 
select		 db_name(),
		 databaseguid
from		 __orm__

/* populate the tables */
insert	@table	([owner], [name], fkdatabaseid, allownew, islookuptable, generatecodefor, accessorname, generatesamplecode  ) 
select 	TABLE_SCHEMA,
	TABLE_NAME,
	1,
	'true',
	'false',
	'true',
	TABLE_NAME,
	'false'
from 	INFORMATION_SCHEMA.TABLES tbl
WHERE 	TABLE_TYPE = 'BASE TABLE'
AND	TABLE_NAME collate sql_latin1_general_cp1_ci_as not in ('dtproperties', '__orm__')   

/* remove duplicate table names not owned by dbo */

DELETE	t2
FROM	@table t1, @table t2
WHERE	t1.[owner] collate sql_latin1_general_cp1_ci_as = 'dbo'
AND	t2.[owner] collate sql_latin1_general_cp1_ci_as <> 'dbo'
AND	t1.[name] = t2.[name]

/* populate the columns */
insert	@column( fktableid, [name], fkdatatypeid, length, primarykey, setaccessor, 
		 getaccessor, requiredforinstantiation, accessorname, nullable, isidentity, isreadonly)
select		t.id, 
		c.name, 
		dt.id, 
		CASE
			WHEN ty.name = 'text' THEN -1 
			WHEN ty.name = 'ntext' THEN -1 
			WHEN ty.name = 'image' THEN -1 
			ELSE c.length
		END, 
		'false',
		'public virtual',
		'public virtual',
		case when c.isnullable = 0 and cc.COLUMN_DEFAULT is null then 'true' else 'false' end,
		c.name,
		case when c.isnullable = 0 then 'false' else 'true' end,
		case when c.autoval is null then 'false' else 'true' end,
		case when c.autoval is not null or c.iscomputed = 1 then 'true' else 'false' end
from		syscolumns c, sysobjects o, systypes ty, @table t, @datatype dt, INFORMATION_SCHEMA.COLUMNS cc
where		o.id = c.id and 
		o.xtype collate sql_latin1_general_cp1_ci_as = 'u' and 
		c.xtype = ty.xtype and 
		t.name = o.name collate sql_latin1_general_cp1_ci_as and
		ty.name = dt.sqltype collate sql_latin1_general_cp1_ci_as and
		cc.COLUMN_NAME = c.name collate sql_latin1_general_cp1_ci_as and
		cc.TABLE_NAME = o.name collate sql_latin1_general_cp1_ci_as and
		cc.TABLE_CATALOG = db_name()
order by	t.id,
		c.colorder


insert	@tmpRelations
select 	fkeys.CONSTRAINT_NAME as [Constraint],refs.UNIQUE_CONSTRAINT_NAME as [PrimaryKeyName],
	fkeys.TABLE_NAME as [ForeignTable], fkeys.COLUMN_NAME as [ForeignColumn],
	pkeys.TABLE_NAME as [PrimaryTable], pkeys.COLUMN_NAME as [PrimaryColumn], 
	refs.UPDATE_RULE as [UpdateRule], refs.DELETE_RULE as [DeleteRule],
	pkeys.ORDINAL_POSITION as [Ordinal]
from 	INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkeys, INFORMATION_SCHEMA.TABLE_CONSTRAINTS fkeycons,
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkeys, INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkeycons,
	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS refs
where	refs.CONSTRAINT_NAME = fkeys.CONSTRAINT_NAME
and	refs.UNIQUE_CONSTRAINT_NAME = pkeys.CONSTRAINT_NAME
and	fkeys.ORDINAL_POSITION = pkeys.ORDINAL_POSITION
and	fkeys.CONSTRAINT_NAME = fkeycons.CONSTRAINT_NAME
and	fkeycons.CONSTRAINT_TYPE = 'FOREIGN KEY'
and	fkeys.TABLE_NAME = fkeycons.TABLE_NAME
and	pkeys.CONSTRAINT_NAME = pkeycons.CONSTRAINT_NAME
and	pkeycons.CONSTRAINT_TYPE = 'PRIMARY KEY'
and	pkeys.TABLE_NAME = pkeycons.TABLE_NAME

update	col
set	primarykey = 'true'
FROM	INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkeys, 
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkeycons,
	@column col, 
	@table tbl
where	pkeys.CONSTRAINT_NAME = pkeycons.CONSTRAINT_NAME
and	pkeycons.CONSTRAINT_TYPE = 'PRIMARY KEY'
and	pkeys.TABLE_NAME = pkeycons.TABLE_NAME
and	tbl.id = col.fktableid
and	tbl.name collate sql_latin1_general_cp1_ci_as = pkeys.TABLE_NAME
and	col.name collate sql_latin1_general_cp1_ci_as = pkeys.COLUMN_NAME


/* update identity columns to have no set accessor, and required for instantiation defaulting to false */
update	@column
set	setaccessor = 'none',
	requiredforinstantiation = 'false'
where	isidentity = 'true'
or	isreadonly = 'true'


insert		@relation ( fkparenttableid, fkchildtableid, foreignkeycolumnname, relationshiptype, parentconstraintname,
			    childconstraintname, childaccessorname, parentaccessorname, childsetaccessor, childgetaccessor, 
			    parentsetaccessor, parentgetaccessor, ismandatory, disassociatechild, cascadedelete, 
		   	    cascadeupdate, enforceconstraint, nullablefkey)
select	
	ptable.id,
	ctable.id,
	tmp.ForeignColumn,
	'1:n',
	tmp.PrimaryKeyName,
	tmp.[Constraint],
	ctable.name,
	ptable.name,
	'public virtual',
	'public virtual',
	'public virtual',
	'public virtual',
	'false',
	'true',
	CASE WHEN tmp.DeleteRule = 'CASCADE' then 'true' ELSE 'false' END,
	CASE WHEN tmp.UpdateRule = 'CASCADE' then 'true' ELSE 'false' END,
	case when objectproperty(object_id(tmp.[Constraint]), 'cnstisdisabled') = 0 then 'true' else 'false' end,
	null
from	@table ptable, @table ctable, @tmpRelations tmp
where	tmp.PrimaryTable = ptable.name
and	tmp.ForeignTable = ctable.name


insert	@relationcolumn( fkrelationid, parentcolumnname, childcolumnname, ordinalposition, nullablefkey, childcolumnisprimarykey)

select	rel.id,
	tmp.PrimaryColumn,
	tmp.ForeignColumn,
	tmp.Ordinal,
	col.nullable,
	'false'
from	@relation rel, @tmpRelations tmp, @column col, @table tbl
where	rel.parentconstraintname = tmp.PrimaryKeyName
and	rel.childconstraintname = tmp.[Constraint]
and	col.name = tmp.ForeignColumn
and	tbl.id = rel.fkchildtableid
and	tbl.id = col.fktableid


update	rc
set	childcolumnisprimarykey = 'true'
from	@relationcolumn rc, @relation r, @table t, @column c
where	rc.fkrelationid = r.id and
	r.fkchildtableid = t.id and
	c.fktableid = t.id and
	c.name = rc.childcolumnname and
	c.primarykey = 'true'

update	r
set	foreignkeycolumnname = rc.childcolumnname ,
	ismandatory = case when rc.nullablefkey = 'true' then 'false' else 'true' end,
	nullablefkey = rc.nullablefkey 
from	@relation r, @relationcolumn rc
where	r.id = rc.fkrelationid
and	foreignkeycolumnname = rc.childcolumnname

update	r
set	childaccessorname = r.childaccessorname + '_by_' + r.foreignkeycolumnname
from	@relation r, @relation r2
where	r.fkparenttableid = r2.fkparenttableid and
	r.fkchildtableid = r2.fkchildtableid and
	r.childaccessorname = r2.childaccessorname and
	r.id <> r2.id

update	r
set	parentaccessorname = r.parentaccessorname + '_by_' + r.foreignkeycolumnname
from	@relation r, @relation r2
where	r.fkparenttableid = r2.fkparenttableid and
	r.fkchildtableid = r2.fkchildtableid and
	r.parentaccessorname = r2.parentaccessorname and
	r.id <> r2.id

update	r1
set	parentaccessorname = r1.parentaccessorname + '_by_' + r1.foreignkeycolumnname,
	childaccessorname = r1.childaccessorname + '_by_primarykey'
from	@relation r1, @relation r2
where	r1.fkparenttableid = r2.fkchildtableid and
	r2.fkparenttableid = r1.fkchildtableid and
	r1.id <> r2.id 

update	c
set	requiredforinstantiation = 'false'
from	@column c, @relation r
where	c.fktableid = r.fkchildtableid and
	c.name = r.foreignkeycolumnname
and	c.primarykey = 'true'

/* delete foreign key columns from column, they should only exist in the relation table 
delete	@column
from	@column c, @relation r
where	c.fktableid = r.fkchildtableid and
	c.name = r.foreignkeycolumnname
and	c.primarykey = 'false'
*/


/* update fkeys to not be required */
update	c
set	requiredforinstantiation = 'false'
from	@column c, @relation r
where	c.fktableid = r.fkchildtableid
and	requiredforinstantiation = 'true'
and	c.name = r.foreignkeycolumnname
and	c.primarykey = 'false'



insert	@procedure([owner], [name], [fkdatabaseid])
select	'dbo',
	o.name,
	1
from	sysobjects o
where	o.xtype collate sql_latin1_general_cp1_ci_as = 'p' and 
	o.status >= 0

insert	@param( fkprocedureid, fkdatatypeid, [name], length, isoutputparam)
select	pr.id,
	dt.id,
	c.name,
	c.length,
	c.isoutparam
from	sysobjects o, syscolumns c, @procedure pr, @datatype dt, systypes t
where	o.id = c.id and
	o.xtype collate sql_latin1_general_cp1_ci_as = 'p' and
	o.status >= 0 and
	o.name = pr.name collate sql_latin1_general_cp1_ci_as and
	c.xtype = t.xtype and
	t.name = dt.sqltype collate sql_latin1_general_cp1_ci_as
order by pr.id,
	 c.colorder	



/* return the results  */
select	* from @database
select	* from @table ORDER BY [name] asc
select	* from @datatype
select	* from @column 
select 	* from @relation 
select	* from @relationcolumn
select	* from @procedure
select	* from @param