60 lines
2.5 KiB
Transact-SQL
60 lines
2.5 KiB
Transact-SQL
DECLARe @AS XML;
|
|
|
|
SET @as = [dbo].[mfr__schema]();
|
|
|
|
DECLARE @types TABLE ([edm] varchar(100), [sql] varchar(50));
|
|
INSERT INTO @types VALUES ('Edm.Boolean', 'bit'),
|
|
('Edm.DateTime', 'DateTime'),
|
|
('Edm.Decimal', 'decimal'),
|
|
('Edm.Double', 'float'),
|
|
('Edm.Int32', 'int'),
|
|
('Edm.Int64', 'bigint'),
|
|
('Edm.String', 'varchar(255)');
|
|
|
|
|
|
--WITH XMLNAMESPACES
|
|
--(
|
|
|
|
-- 'http://schemas.microsoft.com/ado/2007/06/edmx' as edmx,
|
|
-- 'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' as m
|
|
--) , x as (
|
|
-- SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as '#', xD.value('@Name', 'varchar(255)') as 'set', xD.value('End[1]/@Role', 'varchar(255)') as 'role1', xD.value('End[2]/@Role', 'varchar(255)') as 'role2' , xD.query('.') as 'q' from @as.nodes('/Schema/EntityContainer/AssociationSet') as xm(xD)
|
|
--), y as (
|
|
-- select [#], [role1], [role2], RIGHT([set],CHARINDEX('_', REVERSE([set])) - 1) as [name]
|
|
|
|
-- from x
|
|
--), z as(
|
|
-- SELECT [cmd] = N'DROP TABLE IF EXISTS [dbo].[mfr__*' + [name] + N'] GO' + CHAR(10) + ' CREATE TABLE [dbo].[mfr__*' + [name] + N']([' + [role1] + N'] [bigint] NOT NULL,[' + [role2] + N'] [bigint] NOT NULL) ON [PRIMARY] GO' + CHAR(10) FROM y
|
|
--)
|
|
--select TOP( 1000) * from z;
|
|
|
|
|
|
--WITH XMLNAMESPACES
|
|
--(
|
|
|
|
-- 'http://schemas.microsoft.com/ado/2007/06/edmx' as edmx,
|
|
-- 'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' as m
|
|
--), x as (
|
|
-- SELECT xD.value('@Name', 'varchar(255)') as [name], xD.query('.') as 'q' from @as.nodes('/Schema/EntityContainer/EntitySet') as xm(xD)
|
|
--)
|
|
--SELEcT * FROM x
|
|
-- where not exists(select * from sys.objects as o where type_desc = 'USER_TABLE' AND o.[name] = 'mfr__' + x.[name]);
|
|
|
|
|
|
WITH XMLNAMESPACES
|
|
(
|
|
'http://schemas.microsoft.com/ado/2007/06/edmx' as edmx,
|
|
'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' as m
|
|
), np as (
|
|
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as '#', xD.value('../@Name', 'varchar(255)') as [parent], xD.value('@Name', 'varchar(255)') as [name], xD.value('@Type', 'varchar(255)') as [Type], ISNULL(xD.value('@Nullable', 'bit'),1) as [nullable], xD.value('@ToRole', 'varchar(255)') as [ToRole], xD.value('@FromRole', 'varchar(255)') as [FromRole]-- , xD.query('.') as 'q'
|
|
from @as.nodes('/Schema[1]/EntityType/Property') as xm(xD)
|
|
)
|
|
--select distinct [type] from np
|
|
SELECT '[' + [name] + '] '
|
|
+ t.sql
|
|
+ CASE WHEN [nullable] = 0 THEN ' NOT NULL' ELSE '' END
|
|
+ ','
|
|
from np LEFT JOIN @types as t ON np.[Type] = t.[edm]
|
|
where parent = 'StockMovement' order by [#]
|
|
|