MZ @ !L!This program cannot be run in DOS mode.
$ PE L Ԑ\ ! N @
i
@ O x 0> H .text T `.rsrc x @ @.reloc @ B 0 H h P w T
5[
S
ę
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'
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]
, CAST(c.ORDINAL_POSITION as integer) as [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'
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)
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]
, CAST(c.ORDINAL_POSITION as integer) as [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 [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)
SELECT
quotename(SPECIFIC_SCHEMA) + quotename(SPECIFIC_NAME) [Id]
, SPECIFIC_CATALOG [CatalogName]
, SPECIFIC_SCHEMA [SchemaName]
, SPECIFIC_NAME [Name]
, CASE
WHEN DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
CHARACTER_MAXIMUM_LENGTH = -1 THEN
DATA_TYPE + '(max)'
ELSE
DATA_TYPE
END [ReturnTypeName]
, CHARACTER_MAXIMUM_LENGTH [ReturnMaxLength]
, CAST(NUMERIC_PRECISION as integer) [ReturnPrecision]
, CAST(DATETIME_PRECISION as integer) [ReturnDateTimePrecision]
, CAST(NUMERIC_SCALE as integer) [ReturnScale]
, COLLATION_CATALOG [ReturnCollationCatalog]
, COLLATION_SCHEMA [ReturnCollationSchema]
, COLLATION_NAME [ReturnCollationName]
, CHARACTER_SET_CATALOG [ReturnCharacterSetCatalog]
, CHARACTER_SET_SCHEMA [ReturnCharacterSetSchema]
, CHARACTER_SET_NAME [ReturnCharacterSetName]
, CAST(0 as bit) as [ReturnIsMultiSet]
, CAST(0 as bit) as [IsAggregate]
, CAST(0 as bit) as [IsBuiltIn]
, CAST(0 as bit) as [IsNiladic]
, CAST( (CASE WHEN DATA_TYPE = 'TABLE' THEN 1 ELSE 0 END) as bit ) as [IsTvf]
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = 'FUNCTION'
AND NOT (
ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME LIKE 'dt[_]%'
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8
)
SELECT
quotename(f.SPECIFIC_SCHEMA) + quotename(f.SPECIFIC_NAME) + quotename(f.PARAMETER_NAME) [Id]
, quotename(f.SPECIFIC_SCHEMA) + quotename(f.SPECIFIC_NAME) [ParentId]
, CASE -- trim off the @ symbol
WHEN f.PARAMETER_NAME is null THEN NULL
ELSE SUBSTRING(f.PARAMETER_NAME, 2, LEN(f.PARAMETER_NAME) )
END [Name]
, CAST(f.ORDINAL_POSITION as integer) as [Ordinal]
, CASE
WHEN f.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
f.CHARACTER_MAXIMUM_LENGTH = -1 THEN
f.DATA_TYPE + '(max)'
ELSE
f.DATA_TYPE
END [TypeName]
, f.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(f.NUMERIC_PRECISION as integer) [Precision]
, CAST(f.DATETIME_PRECISION as integer) [DateTimePrecision]
, CAST(f.NUMERIC_SCALE as integer) [Scale]
, f.COLLATION_CATALOG [CollationCatalog]
, f.COLLATION_SCHEMA [CollationSchema]
, f.COLLATION_NAME [CollationName]
, f.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, f.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, f.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, f.PARAMETER_MODE [Mode]
, CAST(NULL as ntext) [Default]
FROM
INFORMATION_SCHEMA.PARAMETERS f
INNER JOIN INFORMATION_SCHEMA.ROUTINES r ON
f.SPECIFIC_SCHEMA = r.SPECIFIC_SCHEMA AND
f.SPECIFIC_NAME = r.SPECIFIC_NAME AND
r.ROUTINE_TYPE = 'FUNCTION'
WHERE
f.IS_RESULT = 'NO'
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]
, CAST(c.ORDINAL_POSITION as integer) [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.ROUTINE_COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.ROUTINES f ON
c.TABLE_CATALOG = f.SPECIFIC_CATALOG AND
c.TABLE_SCHEMA = f.SPECIFIC_SCHEMA AND
c.TABLE_NAME = f.SPECIFIC_NAME
SELECT
quotename(SPECIFIC_SCHEMA) + quotename(SPECIFIC_NAME) [Id]
, SPECIFIC_CATALOG [CatalogName]
, SPECIFIC_SCHEMA [SchemaName]
, SPECIFIC_NAME [Name]
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
NOT (ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME LIKE 'dt[_]%'
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
AND (DATA_TYPE != 'TABLE' OR DATA_TYPE is null)
AND ROUTINE_TYPE = 'PROCEDURE'
SELECT
quotename(p.SPECIFIC_SCHEMA) + quotename(p.SPECIFIC_NAME) + quotename(p.PARAMETER_NAME) [Id]
, quotename(p.SPECIFIC_SCHEMA) + quotename(p.SPECIFIC_NAME) [ParentId]
, CASE -- trim off the @ symbol
WHEN p.PARAMETER_NAME is null THEN NULL
ELSE SUBSTRING(p.PARAMETER_NAME, 2, LEN(p.PARAMETER_NAME) )
END [Name]
, CAST(p.ORDINAL_POSITION as integer) as [Ordinal]
, CASE
WHEN p.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
p.CHARACTER_MAXIMUM_LENGTH = -1 THEN
p.DATA_TYPE + '(max)'
ELSE
p.DATA_TYPE
END [TypeName]
, p.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(p.NUMERIC_PRECISION as integer) [Precision]
, CAST(p.DATETIME_PRECISION as integer) [DateTimePrecision]
, CAST(p.NUMERIC_SCALE as integer) [Scale]
, p.COLLATION_CATALOG [CollationCatalog]
, p.COLLATION_SCHEMA [CollationSchema]
, p.COLLATION_NAME [CollationName]
, p.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, p.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, p.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, p.PARAMETER_MODE [Mode]
, CAST(NULL as ntext) [Default]
FROM
INFORMATION_SCHEMA.PARAMETERS p
INNER JOIN INFORMATION_SCHEMA.ROUTINES r ON
p.SPECIFIC_SCHEMA = r.SPECIFIC_SCHEMA AND
p.SPECIFIC_NAME = r.SPECIFIC_NAME AND
r.ROUTINE_TYPE = 'PROCEDURE'
WHERE
p.IS_RESULT = 'NO'
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
SELECT
quotename(cc.CONSTRAINT_SCHEMA) + quotename(cc.CONSTRAINT_NAME) [Id]
, cc.CHECK_CLAUSE [Expression]
FROM
INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
SELECT
quotename(CONSTRAINT_SCHEMA) + quotename(CONSTRAINT_NAME) [ConstraintId]
, quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) + quotename(COLUMN_NAME) [ColumnId]
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
SELECT
quotename(rc.CONSTRAINT_SCHEMA) + quotename(rc.CONSTRAINT_NAME) [Id]
, CAST(rc.UPDATE_RULE as nvarchar(11)) [UpdateRule]
, CAST(rc.DELETE_RULE as nvarchar(11)) [DeleteRule]
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
SELECT
quotename(FC.CONSTRAINT_SCHEMA) + quotename(FC.CONSTRAINT_NAME) + quotename(cast(FC.ORDINAL_POSITION as nvarchar(30))) [Id]
, quotename(PC.TABLE_SCHEMA) + quotename(PC.TABLE_NAME) + quotename(PC.COLUMN_NAME) [ToColumnId]
, quotename(FC.TABLE_SCHEMA) + quotename(FC.TABLE_NAME) + quotename(FC.COLUMN_NAME) [FromColumnId]
, quotename(FC.CONSTRAINT_SCHEMA) + quotename(FC.CONSTRAINT_NAME) [ConstraintId]
, CAST(FC.ORDINAL_POSITION as integer) as [Ordinal]
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PC /* PRIMARY KEY COLS*/
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PC.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PC.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FC /* FOREIGN KEY COLS*/
ON RC.CONSTRAINT_SCHEMA = FC.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FC.CONSTRAINT_NAME
AND PC.ORDINAL_POSITION = FC.ORDINAL_POSITION
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(1)) [Expression]
, CAST(NULL as nvarchar(11)) [UpdateRule]
, CAST(NULL as nvarchar(11)) [DeleteRule]
WHERE 1=2
SELECT
CAST(NULL as nvarchar(1)) [ConstraintId]
, CAST(NULL as nvarchar(1)) [ColumnId]
WHERE 1=2
SELECT
CAST(NULL as nvarchar(1)) [Id]
, CAST(NULL as nvarchar(1)) [ToColumnId]
, CAST(NULL as nvarchar(1)) [FromColumnId]
, CAST(NULL as nvarchar(1)) [ConstraintId]
, 0 [Ordinal]
WHERE 1=2
G
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'
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]
, CAST(c.ORDINAL_POSITION as integer) as [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'
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)
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]
, CAST(c.ORDINAL_POSITION as integer) as [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 [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)
SELECT
quotename(SPECIFIC_SCHEMA) + quotename(SPECIFIC_NAME) [Id]
, SPECIFIC_CATALOG [CatalogName]
, SPECIFIC_SCHEMA [SchemaName]
, SPECIFIC_NAME [Name]
, CASE
WHEN DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
CHARACTER_MAXIMUM_LENGTH = -1 THEN
DATA_TYPE + '(max)'
ELSE
DATA_TYPE
END [ReturnTypeName]
, CHARACTER_MAXIMUM_LENGTH [ReturnMaxLength]
, CAST(NUMERIC_PRECISION as integer) [ReturnPrecision]
, CAST(DATETIME_PRECISION as integer) [ReturnDateTimePrecision]
, CAST(NUMERIC_SCALE as integer) [ReturnScale]
, COLLATION_CATALOG [ReturnCollationCatalog]
, COLLATION_SCHEMA [ReturnCollationSchema]
, COLLATION_NAME [ReturnCollationName]
, CHARACTER_SET_CATALOG [ReturnCharacterSetCatalog]
, CHARACTER_SET_SCHEMA [ReturnCharacterSetSchema]
, CHARACTER_SET_NAME [ReturnCharacterSetName]
, CAST(0 as bit) as [ReturnIsMultiSet]
, CAST(0 as bit) as [IsAggregate]
, CAST(0 as bit) as [IsBuiltIn]
, CAST(0 as bit) as [IsNiladic]
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
NOT (ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME LIKE 'dt[_]%'
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
AND (DATA_TYPE != 'TABLE' OR DATA_TYPE is null)
AND ROUTINE_TYPE = 'FUNCTION'
SELECT
quotename(f.SPECIFIC_SCHEMA) + quotename(f.SPECIFIC_NAME) + quotename(f.PARAMETER_NAME) [Id]
, quotename(f.SPECIFIC_SCHEMA) + quotename(f.SPECIFIC_NAME) [ParentId]
, CASE -- trim off the @ symbol
WHEN f.PARAMETER_NAME is null THEN NULL
ELSE SUBSTRING(f.PARAMETER_NAME, 2, LEN(f.PARAMETER_NAME) )
END [Name]
, CAST(f.ORDINAL_POSITION as integer) as [Ordinal]
, CASE
WHEN f.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
f.CHARACTER_MAXIMUM_LENGTH = -1 THEN
f.DATA_TYPE + '(max)'
ELSE
f.DATA_TYPE
END [TypeName]
, f.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(f.NUMERIC_PRECISION as integer) [Precision]
, CAST(f.DATETIME_PRECISION as integer) [DateTimePrecision]
, CAST(f.NUMERIC_SCALE as integer) [Scale]
, f.COLLATION_CATALOG [CollationCatalog]
, f.COLLATION_SCHEMA [CollationSchema]
, f.COLLATION_NAME [CollationName]
, f.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, f.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, f.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, f.PARAMETER_MODE [Mode]
, CAST(NULL as ntext) [Default]
FROM
INFORMATION_SCHEMA.PARAMETERS f
INNER JOIN INFORMATION_SCHEMA.ROUTINES r ON
f.SPECIFIC_SCHEMA = r.SPECIFIC_SCHEMA AND
f.SPECIFIC_NAME = r.SPECIFIC_NAME AND
r.ROUTINE_TYPE = 'FUNCTION'
WHERE
f.IS_RESULT = 'NO'
SELECT
quotename(SPECIFIC_SCHEMA) + quotename(SPECIFIC_NAME) [Id]
, SPECIFIC_CATALOG [CatalogName]
, SPECIFIC_SCHEMA [SchemaName]
, SPECIFIC_NAME [Name]
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
NOT (ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME LIKE 'dt[_]%'
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
AND (DATA_TYPE != 'TABLE' OR DATA_TYPE is null)
AND ROUTINE_TYPE = 'PROCEDURE'
SELECT
quotename(p.SPECIFIC_SCHEMA) + quotename(p.SPECIFIC_NAME) + quotename(p.PARAMETER_NAME) [Id]
, quotename(p.SPECIFIC_SCHEMA) + quotename(p.SPECIFIC_NAME) [ParentId]
, CASE -- trim off the @ symbol
WHEN p.PARAMETER_NAME is null THEN NULL
ELSE SUBSTRING(p.PARAMETER_NAME, 2, LEN(p.PARAMETER_NAME) )
END [Name]
, CAST(p.ORDINAL_POSITION as integer) as [Ordinal]
, CASE
WHEN p.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
p.CHARACTER_MAXIMUM_LENGTH = -1 THEN
p.DATA_TYPE + '(max)'
ELSE
p.DATA_TYPE
END [TypeName]
, p.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(p.NUMERIC_PRECISION as integer) [Precision]
, CAST(p.DATETIME_PRECISION as integer) [DateTimePrecision]
, CAST(p.NUMERIC_SCALE as integer) [Scale]
, p.COLLATION_CATALOG [CollationCatalog]
, p.COLLATION_SCHEMA [CollationSchema]
, p.COLLATION_NAME [CollationName]
, p.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, p.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, p.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, p.PARAMETER_MODE [Mode]
, CAST(NULL as ntext) [Default]
FROM
INFORMATION_SCHEMA.PARAMETERS p
INNER JOIN INFORMATION_SCHEMA.ROUTINES r ON
p.SPECIFIC_SCHEMA = r.SPECIFIC_SCHEMA AND
p.SPECIFIC_NAME = r.SPECIFIC_NAME AND
r.ROUTINE_TYPE = 'PROCEDURE'
WHERE
p.IS_RESULT = 'NO'
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
SELECT
quotename(cc.CONSTRAINT_SCHEMA) + quotename(cc.CONSTRAINT_NAME) [Id]
, cc.CHECK_CLAUSE [Expression]
FROM
INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
SELECT
quotename(CONSTRAINT_SCHEMA) + quotename(CONSTRAINT_NAME) [ConstraintId]
, quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) + quotename(COLUMN_NAME) [ColumnId]
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
SELECT
quotename(rc.CONSTRAINT_SCHEMA) + quotename(rc.CONSTRAINT_NAME) [Id]
, CAST(rc.UPDATE_RULE as nvarchar(11)) [UpdateRule]
, CAST(rc.DELETE_RULE as nvarchar(11)) [DeleteRule]
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
SELECT
quotename(FC.CONSTRAINT_SCHEMA) + quotename(FC.CONSTRAINT_NAME) + quotename(cast(FC.ORDINAL_POSITION as nvarchar(30))) [Id]
, quotename(PC.TABLE_SCHEMA) + quotename(PC.TABLE_NAME) + quotename(PC.COLUMN_NAME) [ToColumnId]
, quotename(FC.TABLE_SCHEMA) + quotename(FC.TABLE_NAME) + quotename(FC.COLUMN_NAME) [FromColumnId]
, quotename(FC.CONSTRAINT_SCHEMA) + quotename(FC.CONSTRAINT_NAME) [ConstraintId]
, CAST(FC.ORDINAL_POSITION as integer) as [Ordinal]
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PC /* PRIMARY KEY COLS*/
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PC.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PC.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FC /* FOREIGN KEY COLS*/
ON RC.CONSTRAINT_SCHEMA = FC.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FC.CONSTRAINT_NAME
AND PC.ORDINAL_POSITION = FC.ORDINAL_POSITION
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(1)) [Expression]
, CAST(NULL as nvarchar(11)) [UpdateRule]
, CAST(NULL as nvarchar(11)) [DeleteRule]
WHERE 1=2
SELECT
CAST(NULL as nvarchar(1)) [ConstraintId]
, CAST(NULL as nvarchar(1)) [ColumnId]
WHERE 1=2
SELECT
CAST(NULL as nvarchar(1)) [Id]
, CAST(NULL as nvarchar(1)) [ToColumnId]
, CAST(NULL as nvarchar(1)) [FromColumnId]
, CAST(NULL as nvarchar(1)) [ConstraintId]
, 0 [Ordinal]
WHERE 1=2