原文地址：
http://www.cnblogs.com/LingzhiSun/archive/2011/06/13/EFPowerTool_2.html
上次为大家介绍EF Power Tool之后，不少朋友在使用的时候碰到了一些问题曾像我提问。我自己以及同事在使用这个工具时，其实也碰到了一些问题。今天我将和大家一起分享其中2个问题以及相应的原因。
1. EF Power Tool帮助我们生成Code First POCO class时为何只生成了部分代码，甚至所使用的程序集都没有导入？
在输入了相应的数据库Named Instance和登录信息后，EF Power Tool开始为我们创建相应的class。此时使用SQL Server Profiler同时观察SQL Server在本地的Named Instance和SQL Express Instance，我们会发现一系列数据库访问在Named Instance上执行了。
1) 首先是这样一个查询。以上篇文章中的Parent和Child类为例子，会返回这些用户级别表字段的数据。其中每个字段分别表示
C1: 每个字段在表中的序号
CatalogName: 数据库名
SchemaName：表的schema名
Name：表名
C2：字段名
C3：是否为NULL
C4：字段类型
C5：字段最大长度
C6：字段精度
C7：DateTime的精度
C8：字段刻度
C9：是否为Identity
C10：是否为数据库自动生成
C11：是否为主键
SELECT
[Project6].[C2] AS [C1],
[Project6].[CatalogName] AS [CatalogName],
[Project6].[SchemaName] AS [SchemaName],
[Project6].[Name] AS [Name],
[Project6].[C1] AS [C2],
[Project6].[C3] AS [C3],
[Project6].[C4] AS [C4],
[Project6].[C5] AS [C5],
[Project6].[C6] AS [C6],
[Project6].[C7] AS [C7],
[Project6].[C8] AS [C8],
[Project6].[C9] AS [C9],
[Project6].[C10] AS [C10],
[Project6].[C11] AS [C11]
FROM ( SELECT
[Extent1].[CatalogName] AS [CatalogName],
[Extent1].[SchemaName] AS [SchemaName],
[Extent1].[Name] AS [Name],
[UnionAll1].[Name] AS [C1],
[UnionAll1].[Ordinal] AS [C2],
[UnionAll1].[IsNullable] AS [C3],
[UnionAll1].[TypeName] AS [C4],
[UnionAll1].[MaxLength] AS [C5],
[UnionAll1].[Precision] AS [C6],
[UnionAll1].[DateTimePrecision] AS [C7],
[UnionAll1].[Scale] AS [C8],
[UnionAll1].[IsIdentity] AS [C9],
[UnionAll1].[IsStoreGenerated] AS [C10],
CASE WHEN ([Project5].[C2] IS NULL) THEN cast(0 as bit) ELSE [Project5].[C2] END AS [C11]
FROM (
SELECT
quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) [Id]
, TABLE_CATALOG [CatalogName]
, TABLE_SCHEMA [SchemaName]
, TABLE_NAME [Name]
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'
) AS [Extent1]
INNER JOIN (SELECT
[Extent2].[Id] AS [Id],
[Extent2].[Name] AS [Name],
[Extent2].[Ordinal] AS [Ordinal],
[Extent2].[IsNullable] AS [IsNullable],
[Extent2].[TypeName] AS [TypeName],
[Extent2].[MaxLength] AS [MaxLength],
[Extent2].[Precision] AS [Precision],
[Extent2].[DateTimePrecision] AS [DateTimePrecision],
[Extent2].[Scale] AS [Scale],
[Extent2].[IsIdentity] AS [IsIdentity],
[Extent2].[IsStoreGenerated] AS [IsStoreGenerated],
0 AS [C1],
[Extent2].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT as [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_CATALOG = t.TABLE_CATALOG AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME AND
t.TABLE_TYPE = 'BASE TABLE'
) AS [Extent2]
UNION ALL
SELECT
[Extent3].[Id] AS [Id],
[Extent3].[Name] AS [Name],
[Extent3].[Ordinal] AS [Ordinal],
[Extent3].[IsNullable] AS [IsNullable],
[Extent3].[TypeName] AS [TypeName],
[Extent3].[MaxLength] AS [MaxLength],
[Extent3].[Precision] AS [Precision],
[Extent3].[DateTimePrecision] AS [DateTimePrecision],
[Extent3].[Scale] AS [Scale],
[Extent3].[IsIdentity] AS [IsIdentity],
[Extent3].[IsStoreGenerated] AS [IsStoreGenerated],
6 AS [C1],
[Extent3].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.VIEWS v ON
c.TABLE_CATALOG = v.TABLE_CATALOG AND
c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
c.TABLE_NAME = v.TABLE_NAME
WHERE
NOT (v.TABLE_SCHEMA = 'dbo'
AND v.TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent3]) AS [UnionAll1] ON (0 = [UnionAll1].[C1]) AND ([Extent1].[Id] = [UnionAll1].[ParentId])
LEFT OUTER JOIN (SELECT
[UnionAll2].[Id] AS [C1],
cast(1 as bit) AS [C2]
FROM (
SELECT
quotename(tc.CONSTRAINT_SCHEMA) + quotename(tc.CONSTRAINT_NAME) [Id]
, quotename(tc.TABLE_SCHEMA) + quotename(tc.TABLE_NAME) [ParentId]
, tc.CONSTRAINT_NAME [Name]
, tc.CONSTRAINT_TYPE [ConstraintType]
, CAST(CASE tc.IS_DEFERRABLE WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsDeferrable]
, CAST(CASE tc.INITIALLY_DEFERRED WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsInitiallyDeferred]
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE tc.TABLE_NAME IS NOT NULL
) AS [Extent4]
INNER JOIN (SELECT
7 AS [C1],
[Extent5].[ConstraintId] AS [ConstraintId],
[Extent6].[Id] AS [Id]
FROM (
SELECT
quotename(CONSTRAINT_SCHEMA) + quotename(CONSTRAINT_NAME) [ConstraintId]
, quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) + quotename(COLUMN_NAME) [ColumnId]
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
) AS [Extent5]
INNER JOIN (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT as [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_CATALOG = t.TABLE_CATALOG AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME AND
t.TABLE_TYPE = 'BASE TABLE'
) AS [Extent6] ON [Extent6].[Id] = [Extent5].[ColumnId]
UNION ALL
SELECT
11 AS [C1],
[Extent7].[ConstraintId] AS [ConstraintId],
[Extent8].[Id] AS [Id]
FROM (
SELECT
CAST(NULL as nvarchar(1)) [ConstraintId]
, CAST(NULL as nvarchar(max)) [ColumnId]
WHERE 1=2
) AS [Extent7]
INNER JOIN (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.VIEWS v ON
c.TABLE_CATALOG = v.TABLE_CATALOG AND
c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
c.TABLE_NAME = v.TABLE_NAME
WHERE
NOT (v.TABLE_SCHEMA = 'dbo'
AND v.TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent8] ON [Extent8].[Id] = [Extent7].[ColumnId]) AS [UnionAll2] ON (7 = [UnionAll2].[C1]) AND ([Extent4].[Id] = [UnionAll2].[ConstraintId])
WHERE [Extent4].[ConstraintType] = 'PRIMARY KEY' ) AS [Project5] ON [UnionAll1].[Id] = [Project5].[C1]
) AS [Project6]
ORDER BY [Project6].[SchemaName] ASC, [Project6].[Name] ASC, [Project6].[C2] ASC
2) 然后这个查询返回系统级表的字段信息。
SELECT
[Project6].[C2] AS [C1],
[Project6].[CatalogName] AS [CatalogName],
[Project6].[SchemaName] AS [SchemaName],
[Project6].[Name] AS [Name],
[Project6].[C1] AS [C2],
[Project6].[C3] AS [C3],
[Project6].[C4] AS [C4],
[Project6].[C5] AS [C5],
[Project6].[C6] AS [C6],
[Project6].[C7] AS [C7],
[Project6].[C8] AS [C8],
[Project6].[C9] AS [C9],
[Project6].[C10] AS [C10],
[Project6].[C11] AS [C11]
FROM ( SELECT
[Extent1].[CatalogName] AS [CatalogName],
[Extent1].[SchemaName] AS [SchemaName],
[Extent1].[Name] AS [Name],
[UnionAll1].[Name] AS [C1],
[UnionAll1].[Ordinal] AS [C2],
[UnionAll1].[IsNullable] AS [C3],
[UnionAll1].[TypeName] AS [C4],
[UnionAll1].[MaxLength] AS [C5],
[UnionAll1].[Precision] AS [C6],
[UnionAll1].[DateTimePrecision] AS [C7],
[UnionAll1].[Scale] AS [C8],
[UnionAll1].[IsIdentity] AS [C9],
[UnionAll1].[IsStoreGenerated] AS [C10],
CASE WHEN ([Project5].[C2] IS NULL) THEN cast(0 as bit) ELSE [Project5].[C2] END AS [C11]
FROM (
SELECT
quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) [Id]
, TABLE_CATALOG [CatalogName]
, TABLE_SCHEMA [SchemaName]
, TABLE_NAME [Name]
, VIEW_DEFINITION [ViewDefinition]
, CAST( CASE IS_UPDATABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsUpdatable]
FROM
INFORMATION_SCHEMA.VIEWS
WHERE
NOT (TABLE_SCHEMA = 'dbo'
AND TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent1]
INNER JOIN (SELECT
[Extent2].[Id] AS [Id],
[Extent2].[Name] AS [Name],
[Extent2].[Ordinal] AS [Ordinal],
[Extent2].[IsNullable] AS [IsNullable],
[Extent2].[TypeName] AS [TypeName],
[Extent2].[MaxLength] AS [MaxLength],
[Extent2].[Precision] AS [Precision],
[Extent2].[DateTimePrecision] AS [DateTimePrecision],
[Extent2].[Scale] AS [Scale],
[Extent2].[IsIdentity] AS [IsIdentity],
[Extent2].[IsStoreGenerated] AS [IsStoreGenerated],
4 AS [C1],
[Extent2].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT as [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_CATALOG = t.TABLE_CATALOG AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME AND
t.TABLE_TYPE = 'BASE TABLE'
) AS [Extent2]
UNION ALL
SELECT
[Extent3].[Id] AS [Id],
[Extent3].[Name] AS [Name],
[Extent3].[Ordinal] AS [Ordinal],
[Extent3].[IsNullable] AS [IsNullable],
[Extent3].[TypeName] AS [TypeName],
[Extent3].[MaxLength] AS [MaxLength],
[Extent3].[Precision] AS [Precision],
[Extent3].[DateTimePrecision] AS [DateTimePrecision],
[Extent3].[Scale] AS [Scale],
[Extent3].[IsIdentity] AS [IsIdentity],
[Extent3].[IsStoreGenerated] AS [IsStoreGenerated],
0 AS [C1],
[Extent3].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.VIEWS v ON
c.TABLE_CATALOG = v.TABLE_CATALOG AND
c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
c.TABLE_NAME = v.TABLE_NAME
WHERE
NOT (v.TABLE_SCHEMA = 'dbo'
AND v.TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent3]) AS [UnionAll1] ON (0 = [UnionAll1].[C1]) AND ([Extent1].[Id] = [UnionAll1].[ParentId])
LEFT OUTER JOIN (SELECT
[UnionAll2].[Id] AS [C1],
cast(1 as bit) AS [C2]
FROM (
SELECT
CAST(NULL as nvarchar(1)) [Id]
, CAST(NULL as nvarchar(256)) [ParentId]
, CAST(NULL as nvarchar(256)) [Name]
, CAST(NULL as nvarchar(256)) [ConstraintType]
, CAST(0 as bit) [IsDeferrable]
, CAST(0 as bit) [IsInitiallyDeferred]
, CAST(NULL as nvarchar(max)) [Expression]
, CAST(NULL as nvarchar(11)) [UpdateRule]
, CAST(NULL as nvarchar(11)) [DeleteRule]
WHERE 1=2
) AS [Extent4]
INNER JOIN (SELECT
10 AS [C1],
[Extent5].[ConstraintId] AS [ConstraintId],
[Extent6].[Id] AS [Id]
FROM (
SELECT
quotename(CONSTRAINT_SCHEMA) + quotename(CONSTRAINT_NAME) [ConstraintId]
, quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) + quotename(COLUMN_NAME) [ColumnId]
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
) AS [Extent5]
INNER JOIN (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT as [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_CATALOG = t.TABLE_CATALOG AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME AND
t.TABLE_TYPE = 'BASE TABLE'
) AS [Extent6] ON [Extent6].[Id] = [Extent5].[ColumnId]
UNION ALL
SELECT
7 AS [C1],
[Extent7].[ConstraintId] AS [ConstraintId],
[Extent8].[Id] AS [Id]
FROM (
SELECT
CAST(NULL as nvarchar(1)) [ConstraintId]
, CAST(NULL as nvarchar(max)) [ColumnId]
WHERE 1=2
) AS [Extent7]
INNER JOIN (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) +