#regionSQL TemplatesprivateconststringSQL_GetDatabaseName="SELECT db_name()";privateconststringSQL2005_GetTables=@"SELECT
object_name(so.id) AS OBJECT_NAME,
schema_name(so.uid) AS USER_NAME,
so.type AS TYPE,
so.crdate AS DATE_CREATED,
fg.file_group AS FILE_GROUP,
so.id as OBJECT_ID
FROM
dbo.sysobjects so
LEFT JOIN (
SELECT
s.groupname AS file_group,
i.id AS id
FROM dbo.sysfilegroups s
INNER JOIN dbo.sysindexes i
ON i.groupid = s.groupid
WHERE i.indid
) AS fg
ON so.id = fg.id
WHERE
so.type = N'U'
AND permissions(so.id) & 4096 <> 0
AND ObjectProperty(so.id, N'IsMSShipped') = 0
AND NOT EXISTS (SELECT * FROM sys.extended_properties WHERE major_id = so.id AND name = 'microsoft_database_tools_support' AND value = 1)
ORDER BY schema_name(so.uid), object_name(so.id)";privateconststringSQL2000_GetTables=@"SELECT
object_name(so.id) AS OBJECT_NAME,
user_name(so.uid) AS USER_NAME,
so.type AS TYPE,
so.crdate AS DATE_CREATED,
fg.file_group AS FILE_GROUP,
so.id AS OBJECT_ID
FROM
dbo.sysobjects so
LEFT JOIN (
SELECT
s.groupname AS file_group,
i.id AS id
FROM dbo.sysfilegroups s
INNER JOIN dbo.sysindexes i
ON i.groupid = s.groupid
WHERE i.indid
) AS fg
ON so.id = fg.id
WHERE
so.type = N'U'
AND permissions(so.id) & 4096 <> 0
AND ObjectProperty(so.id, N'IsMSShipped') = 0
ORDER BY user_name(so.uid), object_name(so.id)";privateconststringSQL_GetTables=@"SELECT
object_name(id) AS OBJECT_NAME,
user_name(uid) AS USER_NAME,
type AS TYPE,
crdate AS DATE_CREATED,
'' AS FILE_GROUP,
id as OBJECT_ID
FROM
sysobjects
WHERE
type = N'U'
AND permissions(id) & 4096 <> 0
AND ObjectProperty(id, N'IsMSShipped') = 0
ORDER BY user_name(uid), object_name(id)";privateconststringSQL2005_GetTableColumns=@"SELECT
clmns.[name] AS [Name],
usrt.[name] AS [DataType],
ISNULL(baset.[name], N'') AS [SystemType],
CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS int) AS [Length],
CAST(clmns.xprec AS tinyint) AS [NumericPrecision],
CAST(clmns.xscale AS int) AS [NumericScale],
CASE CAST(clmns.isnullable AS bit) WHEN 1 THEN 'YES' ELSE 'NO' END AS [Nullable],
defaults.text AS [DefaultValue],
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int) AS [Identity],
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsRowGuidCol') AS int) AS IsRowGuid,
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsComputed') AS int) AS IsComputed,
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsDeterministic') AS int) AS IsDeterministic,
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(QUOTENAME(SCHEMA_NAME(tbl.uid)) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS nvarchar(40)) AS [IdentitySeed],
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(QUOTENAME(SCHEMA_NAME(tbl.uid)) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS nvarchar(40)) AS [IdentityIncrement],
cdef.[text] AS ComputedDefinition,
clmns.[collation] AS Collation,
CAST(clmns.colid AS int) AS ObjectId
FROM
dbo.sysobjects AS tbl
INNER JOIN dbo.syscolumns AS clmns ON clmns.id=tbl.id
LEFT JOIN dbo.systypes AS usrt ON usrt.xusertype = clmns.xusertype
LEFT JOIN dbo.sysusers AS sclmns ON sclmns.uid = usrt.uid
LEFT JOIN dbo.systypes AS baset ON baset.xusertype = clmns.xtype and baset.xusertype = baset.xtype
LEFT JOIN dbo.syscomments AS defaults ON defaults.id = clmns.cdefault
LEFT JOIN dbo.syscomments AS cdef ON cdef.id = clmns.id AND cdef.number = clmns.colid
WHERE
(tbl.[type] = 'U' OR tbl.[type] = 'S')
AND SCHEMA_NAME(tbl.uid) = @SchemaName
AND tbl.[name] = @TableName
ORDER BY
clmns.colorder";privateconststringSQL2000_GetTableColumns=@"SELECT
clmns.[name] AS [Name],
usrt.[name] AS [DataType],
ISNULL(baset.[name], N'') AS [SystemType],
CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS INT) AS [Length],
CAST(clmns.xprec AS TINYINT) AS [NumericPrecision],
CAST(clmns.xscale AS INT) AS [NumericScale],
CASE CAST(clmns.isnullable AS BIT) WHEN 1 THEN 'YES' ELSE 'NO' END AS [Nullable],
defaults.text AS [DefaultValue],
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS INT) AS [Identity],
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsRowGuidCol') AS INT) AS IsRowGuid,
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsComputed') AS INT) AS IsComputed,
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsDeterministic') AS INT) AS IsDeterministic,
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(QUOTENAME(stbl.[name]) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)) AS [IdentitySeed],
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(QUOTENAME(stbl.[name]) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)) AS [IdentityIncrement],
cdef.[text] AS ComputedDefinition,
clmns.[collation] AS Collation,
CAST(clmns.colid AS int) AS ObjectId
FROM
dbo.sysobjects AS tbl
INNER JOIN dbo.sysusers AS stbl ON stbl.[uid] = tbl.[uid]
INNER JOIN dbo.syscolumns AS clmns ON clmns.id=tbl.id
LEFT JOIN dbo.systypes AS usrt ON usrt.xusertype = clmns.xusertype
LEFT JOIN dbo.sysusers AS sclmns ON sclmns.uid = usrt.uid
LEFT JOIN dbo.systypes AS baset ON baset.xusertype = clmns.xtype and baset.xusertype = baset.xtype
LEFT JOIN dbo.syscomments AS defaults ON defaults.id = clmns.cdefault
LEFT JOIN dbo.syscomments AS cdef ON cdef.id = clmns.id AND cdef.number = clmns.colid
WHERE
(tbl.[type] = 'U' OR tbl.[type] = 'S')
AND stbl.[name] = @SchemaName
AND tbl.[name] = @TableName
ORDER BY
clmns.colorder";privateconststringSQL_GetTableColumns=@"SELECT
cols.COLUMN_NAME,
CASE
WHEN cols.DOMAIN_NAME IS NOT NULL THEN cols.DOMAIN_NAME
ELSE cols.DATA_TYPE
END
AS DATA_TYPE,
cols.DATA_TYPE AS UNDERLYING_TYPE,
CAST(cols.CHARACTER_MAXIMUM_LENGTH AS int),
cols.NUMERIC_PRECISION,
cols.NUMERIC_SCALE,
cols.IS_NULLABLE,
cols.COLUMN_DEFAULT,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsIdentity') AS IS_IDENTITY,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsRowGuidCol') AS IS_ROW_GUID_COL,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsComputed') AS IS_COMPUTED,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'),cols.COLUMN_NAME,'IsDeterministic') AS IS_DETERMINISTIC,
CASE WHEN (COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'), cols.COLUMN_NAME, N'IsIdentity') <> 0) then CONVERT(nvarchar(40), ident_seed(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']')) else null end AS IDENTITY_SEED,
CASE WHEN (COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']'), cols.COLUMN_NAME, N'IsIdentity') <> 0) then CONVERT(nvarchar(40), ident_incr(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @TableName + N']')) else null end AS IDENTITY_INCREMENT,
NULL AS COMPUTED_DEFINITION,
NULL AS [collation],
CAST(0 AS int) AS ObjectId
FROM
INFORMATION_SCHEMA.COLUMNS cols
WHERE
cols.TABLE_CATALOG = @DatabaseName
AND cols.TABLE_SCHEMA = @OwnerName
AND cols.TABLE_NAME = @TableName
ORDER BY
cols.ORDINAL_POSITION";privateconststringSQL2005_GetViews=@"SELECT
object_name(id) AS OBJECT_NAME,
schema_name(uid) AS USER_NAME,
type AS TYPE,
crdate AS DATE_CREATED,
id as OBJECT_ID
FROM
sysobjects
WHERE
type = N'V'
AND permissions(id) & 4096 <> 0
AND ObjectProperty(id, N'IsMSShipped') = 0
AND NOT EXISTS (SELECT * FROM sys.extended_properties WHERE major_id = id AND name = 'microsoft_database_tools_support' AND value = 1)
ORDER BY object_name(id)";privateconststringSQL_GetViews=@"SELECT
object_name(id) AS OBJECT_NAME,
user_name(uid) AS USER_NAME,
type AS TYPE,
crdate AS DATE_CREATED,
id as OBJECT_ID
FROM
sysobjects
WHERE
type = N'V'
AND permissions(id) & 4096 <> 0
AND ObjectProperty(id, N'IsMSShipped') = 0
ORDER BY object_name(id)";privateconststringSQL2000_GetViewColumns=@"SELECT
cols.COLUMN_NAME,
CASE
WHEN cols.DOMAIN_NAME IS NOT NULL THEN cols.DOMAIN_NAME COLLATE Latin1_General_BIN
ELSE cols.DATA_TYPE
END
AS DATA_TYPE,
cols.DATA_TYPE AS UNDERLYING_TYPE,
CAST(cols.CHARACTER_MAXIMUM_LENGTH AS int),
cols.NUMERIC_PRECISION,
cols.NUMERIC_SCALE,
cols.IS_NULLABLE,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @ViewName + N']'),cols.COLUMN_NAME,'IsComputed') AS IS_COMPUTED,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @ViewName + N']'),cols.COLUMN_NAME,'IsDeterministic') AS IS_DETERMINISTIC
FROM
INFORMATION_SCHEMA.COLUMNS cols
WHERE
cols.TABLE_CATALOG = @DatabaseName
AND cols.TABLE_SCHEMA = @OwnerName
AND cols.TABLE_NAME = @ViewName
ORDER BY
cols.ORDINAL_POSITION";privateconststringSQL_GetViewColumns=@"SELECT
cols.COLUMN_NAME,
CASE
WHEN cols.DOMAIN_NAME IS NOT NULL THEN cols.DOMAIN_NAME
ELSE cols.DATA_TYPE
END
AS DATA_TYPE,
cols.DATA_TYPE AS UNDERLYING_TYPE,
CAST(cols.CHARACTER_MAXIMUM_LENGTH AS int),
cols.NUMERIC_PRECISION,
cols.NUMERIC_SCALE,
cols.IS_NULLABLE,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @ViewName + N']'),cols.COLUMN_NAME,'IsComputed') AS IS_COMPUTED,
COLUMNPROPERTY(OBJECT_ID(N'[' + @DatabaseName + N'].[' + @OwnerName + N'].[' + @ViewName + N']'),cols.COLUMN_NAME,'IsDeterministic') AS IS_DETERMINISTIC
FROM
INFORMATION_SCHEMA.COLUMNS cols
WHERE
cols.TABLE_CATALOG = @DatabaseName
AND cols.TABLE_SCHEMA = @OwnerName
AND cols.TABLE_NAME = @ViewName
ORDER BY
cols.ORDINAL_POSITION";privateconststringSQL_GetTablePrimaryKey="EXEC sp_MStablekeys @tablename";privateconststringSQL_GetTableIndexes=@"SELECT
i.name,
i.status,
i.indid,
i.OrigFillFactor,
IndCol1 = INDEX_COL(@tablename, i.indid, 1),
IndCol2 = INDEX_COL(@tablename, i.indid, 2),
IndCol3 = INDEX_COL(@tablename, i.indid, 3),
IndCol4 = INDEX_COL(@tablename, i.indid, 4),
IndCol5 = INDEX_COL(@tablename, i.indid, 5),
IndCol6 = INDEX_COL(@tablename, i.indid, 6),
IndCol7 = INDEX_COL(@tablename, i.indid, 7),
IndCol8 = INDEX_COL(@tablename, i.indid, 8),
IndCol9 = INDEX_COL(@tablename, i.indid, 9),
IndCol10 = INDEX_COL(@tablename, i.indid, 10),
IndCol11 = INDEX_COL(@tablename, i.indid, 11),
IndCol12 = INDEX_COL(@tablename, i.indid, 12),
IndCol13 = INDEX_COL(@tablename, i.indid, 13),
IndCol14 = INDEX_COL(@tablename, i.indid, 14),
IndCol15 = INDEX_COL(@tablename, i.indid, 15),
IndCol16 = INDEX_COL(@tablename, i.indid, 16),
IsDescCol1 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 1, N'isdescending'),
IsDescCol2 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 2, N'isdescending'),
IsDescCol3 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 3, N'isdescending'),
IsDescCol4 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 4, N'isdescending'),
IsDescCol5 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 5, N'isdescending'),
IsDescCol6 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 6, N'isdescending'),
IsDescCol7 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 7, N'isdescending'),
IsDescCol8 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 8, N'isdescending'),
IsDescCol9 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 9, N'isdescending'),
IsDescCol10 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 10, N'isdescending'),
IsDescCol11 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 11, N'isdescending'),
IsDescCol12 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 12, N'isdescending'),
IsDescCol13 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 13, N'isdescending'),
IsDescCol14 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 14, N'isdescending'),
IsDescCol15 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 15, N'isdescending'),
IsDescCol16 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 16, N'isdescending'),
IsCompCol1 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 1), N'IsComputed'),
IsCompCol2 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 2), N'IsComputed'),
IsCompCol3 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 3), N'IsComputed'),
IsCompCol4 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 4), N'IsComputed'),
IsCompCol5 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 5), N'IsComputed'),
IsCompCol6 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 6), N'IsComputed'),
IsCompCol7 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 7), N'IsComputed'),
IsCompCol8 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 8), N'IsComputed'),
IsCompCol9 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 9), N'IsComputed'),
IsCompCol10 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 10), N'IsComputed'),
IsCompCol11 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 11), N'IsComputed'),
IsCompCol12 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 12), N'IsComputed'),
IsCompCol13 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 13), N'IsComputed'),
IsCompCol14 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 14), N'IsComputed'),
IsCompCol15 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 15), N'IsComputed'),
IsCompCol16 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 16), N'IsComputed'),
SegName = s.groupname,
IsFullTextKey = INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsFulltextKey'),
IsTable = OBJECTPROPERTY(OBJECT_ID(@tablename), N'IsTable'),
IsStatistics = INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsStatistics'),
IsAutoStatistics = INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsAutoStatistics'),
IsHypothetical = INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsHypothetical'),
IsConstraint = CASE WHEN c.constid IS NOT NULL THEN 1 ELSE 0 END