Minimalistic MSSQL table schema dump script

dum_table_schema.sql

/*
TABLE OPTIONS   
This options should be used for :
	- FileTable
	- Partitioning and Filegroups
	- WITH options
	- TEXTIMAGE_ON
	- FILESTREAM_ON
*/

declare 	
	@_msparam_0 int = 2,
	@_msparam_1 nvarchar(max) = N'posts',
	@_msparam_2 nvarchar(max) = N'dbo'

create table #external_data (
	object_id int not null, 
	data_source_id int , 
	file_format_id int , 
	[location] nvarchar(4000), 
	reject_type nvarchar(20),
	reject_value float, 
	reject_sample_value float, 
	datasource_name nvarchar(128),
	fileformat_name nvarchar(128),
	sharding_col_id int, 
	distribution_type tinyint, 
	remote_schema_name nvarchar(128),
	remote_object_name nvarchar(128)
)

insert into #external_data
select 
	tbl.object_id, eds.data_source_id, eff.file_format_id, et.location, et.reject_type, 
	et.reject_value, et.reject_sample_value, eds.name, eff.name, et.sharding_col_id, 
	et.distribution_type, et.remote_schema_name, et.remote_object_name
from sys.tables tbl
	LEFT OUTER JOIN sys.external_tables AS et ON et.object_id = tbl.object_id
	LEFT OUTER JOIN sys.external_data_sources AS eds ON eds.data_source_id = et.data_source_id
	LEFT OUTER JOIN sys.external_file_formats AS eff ON eff.file_format_id = et.file_format_id
WHERE
	(tbl.is_external = 1)
      


SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Schema],
	tbl.name AS [Name],
	tbl.uses_ansi_nulls AS [AnsiNullsStatus],
	CAST(case when ctt.object_id is null then 0 else 1  end AS bit) AS [ChangeTrackingEnabled],
	ISNULL(edt.datasource_name, N'') AS [DataSourceName],
	case when (tbl.durability=1) then 0 else 1 end AS [Durability],

			CASE
			WHEN edt.distribution_type IS NOT NULL THEN edt.distribution_type
			ELSE 255
			END
		   AS [ExternalTableDistribution],
	ISNULL(edt.fileformat_name,N'') AS [FileFormatName],

			CASE WHEN ((SELECT tblidx.is_memory_optimized FROM sys.tables tblidx WHERE tblidx.object_id = idx.object_id)=1 or
			(SELECT ttidx.is_memory_optimized FROM sys.table_types ttidx WHERE ttidx.type_table_object_id = idx.object_id)=1)
			THEN ISNULL((SELECT ds.name FROM sys.data_spaces AS ds WHERE ds.type='FX'), N'')
			ELSE CASE WHEN 'FG'=dsidx.type THEN dsidx.name ELSE N'' END
			END
		   AS [FileGroup],
	CASE WHEN 'FD'=dstbl.type THEN dstbl.name ELSE N'' END AS [FileStreamFileGroup],
	CASE WHEN 'PS'=dstbl.type THEN dstbl.name ELSE N'' END AS [FileStreamPartitionScheme],
	ISNULL(ft.directory_name,N'') AS [FileTableDirectoryName],
	ISNULL(ft.filename_collation_name,N'') AS [FileTableNameColumnCollation],
	CAST(ISNULL(ft.is_enabled,0) AS bit) AS [FileTableNamespaceEnabled],
	CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusteredIndex],
	CAST(CASE idx.type WHEN 5 THEN 1 ELSE 0 END AS bit) AS [HasClusteredColumnStoreIndex],
	CAST(CASE idx.type WHEN 0 THEN 1 ELSE 0 END AS bit) AS [HasHeapIndex],
	CAST(CASE WHEN periods.start_column_id IS NULL THEN 0 ELSE 1 END AS bit) AS [HasSystemTimePeriod],
	CAST(ISNULL(historyTable.name, N'') AS sysname) AS [HistoryTableName],
	CAST(ISNULL(SCHEMA_NAME(historyTable.schema_id), N'') AS sysname) AS [HistoryTableSchema],
	tbl.object_id AS [ID],
	CAST(tbl.is_edge AS bit) AS [IsEdge],
	CAST(tbl.is_external AS bit) AS [IsExternal],
	tbl.is_filetable AS [IsFileTable],
	CAST(tbl.is_memory_optimized AS bit) AS [IsMemoryOptimized],
	CAST(tbl.is_node AS bit) AS [IsNode],
	CAST(case when tbl.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],
	CAST(
	 case 
		when tbl.is_ms_shipped = 1 then 1
		when (
			select 
				major_id 
			from 
				sys.extended_properties 
			where 
				major_id = tbl.object_id and 
				minor_id = 0 and 
				class = 1 and 
				name = N'microsoft_database_tools_support') 
			is not null then 1
		else 0
	end          
				 AS bit) AS [IsSystemObject],
	CAST(CASE tbl.temporal_type WHEN 2 THEN 1 ELSE 0 END AS bit) AS [IsSystemVersioned],
	CAST(CASE WHEN 'PS'=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],
	CAST(
			case
			when databasepropertyex(DB_NAME(), 'version') < 612 then 0
			else ISNULL(objectproperty(tbl.object_id, 'TableHasVarDecimalStorageFormat'),0)
			end
		   AS bit) AS [IsVarDecimalStorageFormatEnabled],
	ISNULL(edt.location,N'') AS [Location],
	tbl.lock_escalation AS [LockEscalation],
	ISNULL(stbl.name, N'') AS [Owner],
	CASE WHEN 'PS'=dsidx.type THEN dsidx.name ELSE N'' END AS [PartitionScheme],
	ISNULL(edt.reject_sample_value,-1) AS [RejectSampleValue],

			CASE WHEN edt.reject_type IS NULL THEN 255
			ELSE CASE edt.reject_type
			WHEN 'VALUE'      THEN 0
			WHEN 'PERCENTAGE' THEN 1
			ELSE 255
			END
			END
		   AS [RejectType],
	ISNULL(edt.reject_value,0) AS [RejectValue],
	CAST(ISNULL(OBJECTPROPERTY(tbl.object_id,N'IsQuotedIdentOn'),0) AS bit) AS [QuotedIdentifierStatus],
	ISNULL(edt.remote_object_name,'') AS [RemoteObjectName],
	ISNULL(edt.remote_schema_name,'') AS [RemoteSchemaName],

			CASE WHEN edt.sharding_col_id IS NULL THEN N''
			ELSE (select name from sys.columns sc where sc.object_id = tbl.object_id AND sc.column_id = edt.sharding_col_id)
			END
		   AS [ShardingColumnName],
	CAST(
			ISNULL((SELECT cols.name
			FROM sys.columns cols
			WHERE periods.object_id = tbl.object_id
			AND cols.object_id = tbl.object_id
			AND cols.column_id = periods.end_column_id), N'')
		   AS sysname) AS [SystemTimePeriodEndColumn],
	CAST(
			ISNULL((SELECT cols.name
			FROM sys.columns cols
			WHERE periods.object_id = tbl.object_id
			AND cols.object_id = tbl.object_id
			AND cols.column_id = periods.start_column_id), N'')
		   AS sysname) AS [SystemTimePeriodStartColumn],
	tbl.temporal_type AS [TemporalType],
	ISNULL(dstext.name,N'') AS [TextFileGroup],
	CAST(ISNULL(ctt.is_track_columns_updated_on,0) AS bit) AS [TrackColumnsUpdatedEnabled]
FROM
	sys.tables AS tbl
	LEFT OUTER JOIN sys.change_tracking_tables AS ctt ON ctt.object_id = tbl.object_id 
	LEFT OUTER JOIN #external_data AS edt ON edt.object_id = tbl.object_id
	INNER JOIN sys.indexes AS idx ON 
			idx.object_id = tbl.object_id and (idx.index_id < @_msparam_0  or (tbl.is_memory_optimized = 1 and idx.index_id = (select min(index_id) from sys.indexes where object_id = tbl.object_id)))
      
	LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id
	LEFT OUTER JOIN sys.tables AS t ON t.object_id = idx.object_id
	LEFT OUTER JOIN sys.data_spaces AS dstbl ON dstbl.data_space_id = t.Filestream_data_space_id and (idx.index_id < 2 or (idx.type = 7 and idx.index_id < 3))
	LEFT OUTER JOIN sys.filetables AS ft ON ft.object_id = tbl.object_id
	LEFT OUTER JOIN sys.periods as periods ON periods.object_id = tbl.object_id
	LEFT OUTER JOIN sys.tables as historyTable ON historyTable.object_id = tbl.history_table_id
	LEFT OUTER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, 'OwnerId')))
	LEFT OUTER JOIN sys.data_spaces AS dstext  ON tbl.lob_data_space_id = dstext.data_space_id
WHERE
	(tbl.name=@_msparam_1 and SCHEMA_NAME(tbl.schema_id)=@_msparam_2)
ORDER BY
	[Database_Name] ASC,[Schema] ASC,[Name] ASC
OPTION (FORCE ORDER)

DROP TABLE #external_data
      
GO


/*
	Query that can extract column definitions without
	- Default constraints
	- Any index information
	- Check Constraints

	For addtitional info, see queries that coresponds to additional table, index or constraint schema data
*/

declare 	
	@_msparam_0 nvarchar(max) = N'posts',
	@_msparam_1 nvarchar(max) = N'dbo'

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	clmns.column_id AS [ID],
	clmns.name AS [Name],
	clmns.is_ansi_padded AS [AnsiPaddingStatus],
	ISNULL(clmns.collation_name, N'') AS [Collation],
	clmns.column_encryption_key_id AS [ColumnEncryptionKeyID],
	ceks.name AS [ColumnEncryptionKeyName],
	clmns.is_computed AS [Computed],
	ISNULL(cc.definition,N'') AS [ComputedText],
	s1clmns.name AS [DataTypeSchema],
	(case when clmns.default_object_id = 0 then N'' when d.parent_object_id > 0 then N'' else d.name end) AS [Default],
	ISNULL(dc.Name, N'') AS [DefaultConstraintName],
	(case when clmns.default_object_id = 0 then N'' when d.parent_object_id > 0 then N'' else schema_name(d.schema_id) end) AS [DefaultSchema],
	clmns.encryption_algorithm_name AS [EncryptionAlgorithm],
	CAST(clmns.encryption_type AS int) AS [EncryptionType],
	clmns.generated_always_type AS [GeneratedAlwaysType],
	ISNULL(clmns.graph_type, 0) AS [GraphType],
	clmns.is_identity AS [Identity],
	CAST(ISNULL(ic.seed_value,0) AS numeric(38)) AS [IdentitySeedAsDecimal],
	CAST(ISNULL(ic.increment_value,0) AS numeric(38)) AS [IdentityIncrementAsDecimal],
	CAST(0 AS bit) AS [IsClassified],
	CAST(clmns.is_column_set AS bit) AS [IsColumnSet],
	CAST(clmns.is_filestream AS bit) AS [IsFileStream],
	CAST(ISNULL((select TOP 1 1 from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), 0) AS bit) AS [IsForeignKey],
	CAST(clmns.is_masked AS bit) AS [IsMasked],
	CAST(ISNULL(cc.is_persisted, 0) AS bit) AS [IsPersisted],
	CAST(clmns.is_sparse AS bit) AS [IsSparse],
	CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
	ISNULL((SELECT ms.masking_function FROM sys.masked_columns ms WHERE ms.object_id = clmns.object_id AND ms.column_id = clmns.column_id), N'') AS [MaskingFunction],
	ISNULL(ic.is_not_for_replication, 0) AS [NotForReplication],
	clmns.is_nullable AS [Nullable],
	CAST(clmns.scale AS int) AS [NumericScale],
	CAST(clmns.precision AS int) AS [NumericPrecision],
	CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],
	(case when clmns.rule_object_id = 0 then N'' else r.name end) AS [Rule],
	(case when clmns.rule_object_id = 0 then N'' else schema_name(r.schema_id) end) AS [RuleSchema],
	ISNULL(baset.name, N'') AS [SystemType],
	ISNULL(xscclmns.name, N'') AS [XmlSchemaNamespace],
	ISNULL(s2clmns.name, N'') AS [XmlSchemaNamespaceSchema],
	ISNULL( (case clmns.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
	usrt.name AS [DataType]
FROM
	sys.tables AS tbl
	INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
	LEFT OUTER JOIN sys.column_encryption_keys AS ceks ON (ceks.column_encryption_key_id = clmns.column_encryption_key_id)
	LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_id
	LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
	LEFT OUTER JOIN sys.schemas AS s1clmns ON s1clmns.schema_id = usrt.schema_id
	LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id
	LEFT OUTER JOIN sys.default_constraints as dc ON clmns.default_object_id = dc.object_id
	LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
	LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
	LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.rule_object_id
	LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
	LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
WHERE
	(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[ID] ASC
OPTION (FORCE ORDER)
GO

--- TABLE COLUMN EXTENDED PROPERTIES

declare 	
	@_msparam_0 nvarchar(max) = N'posts',
	@_msparam_1 nvarchar(max) = N'dbo'

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	clmns.column_id AS [Column_ID],
	clmns.name AS [Column_Name],
	p.name AS [Name],
	CAST(p.value AS sql_variant) AS [Value]
FROM
	sys.tables AS tbl
	INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
	INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=clmns.column_id AND p.class=1
WHERE
	(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Column_ID] ASC,[Name] ASC
OPTION (FORCE ORDER)

GO

---- TABLE COLUMN DEFAULT CONSTRAINTS filetable_system_defined_objects
declare 	
	@_msparam_0 nvarchar(max) = N'posts',
	@_msparam_1 nvarchar(max) = N'dbo'

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	clmns.column_id AS [Column_ID],
	clmns.name AS [Column_Name],
	cstr.name AS [Name],
	CAST(cstr.is_system_named AS bit) AS [IsSystemNamed],
	CAST(CASE WHEN filetableobj.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS [IsFileTableDefined],
	cstr.definition AS [Text]
FROM
	sys.tables AS tbl
	INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
	INNER JOIN sys.default_constraints AS cstr ON cstr.object_id=clmns.default_object_id
	LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON filetableobj.object_id = cstr.object_id
WHERE
	(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Column_ID] ASC,[Name] ASC
OPTION (FORCE ORDER)

GO

---- TABLE COLUMN DEFAULT CONSTRAINTS EXTENDED PROPERTIES

declare 	
	@_msparam_0 nvarchar(max) = N'posts',
	@_msparam_1 nvarchar(max) = N'dbo'

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	clmns.column_id AS [Column_ID],
	clmns.name AS [Column_Name],
	cstr.name AS [Default_Name],
	p.name AS [Name],
	-- NOTE: I'm not usre if TDS driver can understand sql_variant, but as I recall, 
	-- even if COLUMN_METADATA TYPE_INFO desctibes sql_variant value should hold token type of the acctual type
	CAST(p.value AS sql_variant) AS [Value] 
FROM
	sys.tables AS tbl
	INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
	INNER JOIN sys.default_constraints AS cstr ON cstr.object_id=clmns.default_object_id
	INNER JOIN sys.extended_properties AS p ON p.major_id=cstr.object_id AND p.minor_id=0 AND p.class=1
WHERE
	(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Column_ID] ASC,[Default_Name] ASC,[Name] ASC
OPTION (FORCE ORDER)
GO

----- TABLE INDEXES TYPE AND OPTIONS includes PK, UNIQUE, CLUSTERED, NONCLUSTERED, COLUMNSTORE ...
declare 
	@_msparam_0 int = 0,
	@_msparam_1 bit = 0,
	@_msparam_2 nvarchar(max) = N'posts',
	@_msparam_3 nvarchar(max) = N'dbo'

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	i.name AS [Name],
	CAST(ISNULL(si.bounding_box_xmax,0) AS float(53)) AS [BoundingBoxXMax],
	CAST(ISNULL(si.bounding_box_xmin,0) AS float(53)) AS [BoundingBoxXMin],
	CAST(ISNULL(si.bounding_box_ymax,0) AS float(53)) AS [BoundingBoxYMax],
	CAST(ISNULL(si.bounding_box_ymin,0) AS float(53)) AS [BoundingBoxYMin],
	CAST(case when (i.type=7) then hi.bucket_count else 0 end AS int) AS [BucketCount],
	CAST(ISNULL(si.cells_per_object,0) AS int) AS [CellsPerObject],
	CAST(i.compression_delay AS int) AS [CompressionDelay],
	~i.allow_page_locks AS [DisallowPageLocks],
	~i.allow_row_locks AS [DisallowRowLocks],
	CASE 
		WHEN ((SELECT tbli.is_memory_optimized FROM sys.tables tbli WHERE tbli.object_id = i.object_id)=1 or
			  (SELECT tti.is_memory_optimized FROM sys.table_types tti WHERE tti.type_table_object_id = i.object_id)=1
			) THEN ISNULL((SELECT ds.name FROM sys.data_spaces AS ds WHERE ds.type='FX'), N'')
		ELSE CASE WHEN 'FG' = dsi.type THEN dsi.name ELSE N'' END
	END AS [FileGroup],
	CASE WHEN 'FD'=dstbl.type THEN dstbl.name ELSE N'' END AS [FileStreamFileGroup],
	CASE WHEN 'PS'=dstbl.type THEN dstbl.name ELSE N'' END AS [FileStreamPartitionScheme],
	i.fill_factor AS [FillFactor],
	ISNULL(i.filter_definition, N'') AS [FilterDefinition],
	i.ignore_dup_key AS [IgnoreDuplicateKeys],
	ISNULL(indexedpaths.name, N'') AS [IndexedXmlPathName],
	i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],
	CAST(CASE i.type  
			WHEN 1 THEN 0 
			WHEN 4 THEN 4
			WHEN 3 THEN (CASE xi.xml_index_type WHEN 0 THEN 2 WHEN 1 THEN 3 WHEN 2 THEN 7 WHEN 3 THEN 8 END)
			WHEN 4 THEN 4 
			WHEN 6 THEN 5 
			WHEN 7 THEN 6 
			WHEN 5 THEN 9 
			ELSE 1 
		 END
		 AS tinyint) AS [IndexType],
	CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
	i.is_disabled AS [IsDisabled],
	CAST(CASE WHEN filetableobj.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS [IsFileTableDefined],
	CAST(ISNULL(k.is_system_named, 0) AS bit) AS [IsSystemNamed],
	CAST(OBJECTPROPERTY(i.object_id,N'IsMSShipped') AS bit) AS [IsSystemObject],
	i.is_unique AS [IsUnique],
	CAST(ISNULL(si.level_1_grid,0) AS smallint) AS [Level1Grid],
	CAST(ISNULL(si.level_2_grid,0) AS smallint) AS [Level2Grid],
	CAST(ISNULL(si.level_3_grid,0) AS smallint) AS [Level3Grid],
	CAST(ISNULL(si.level_4_grid,0) AS smallint) AS [Level4Grid],
	ISNULL(s.no_recompute,0) AS [NoAutomaticRecomputation],
	CAST(ISNULL(INDEXPROPERTY(i.object_id, i.name, N'IsPadIndex'), 0) AS bit) AS [PadIndex],
	ISNULL(xi2.name, N'') AS [ParentXmlIndex],
	CASE WHEN 'PS'=dsi.type THEN dsi.name ELSE N'' END AS [PartitionScheme],
	case UPPER(ISNULL(xi.secondary_type,'')) when 'P' then 1 when 'V' then 2 when 'R' then 3 else 0 end AS [SecondaryXmlIndexType],
	CAST(ISNULL(spi.spatial_index_type,0) AS tinyint) AS [SpatialIndexType]
FROM
	sys.tables AS tbl
	INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
	LEFT OUTER JOIN sys.spatial_index_tessellations as si ON i.object_id = si.object_id and i.index_id = si.index_id
	LEFT OUTER JOIN sys.hash_indexes AS hi ON i.object_id = hi.object_id AND i.index_id = hi.index_id
	LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
	LEFT OUTER JOIN sys.tables AS t ON t.object_id = i.object_id
	LEFT OUTER JOIN sys.data_spaces AS dstbl ON dstbl.data_space_id = t.Filestream_data_space_id and (i.index_id < 2 or (i.type = 7 and i.index_id < 3))
	LEFT OUTER JOIN sys.xml_indexes AS xi ON xi.object_id = i.object_id AND xi.index_id = i.index_id
	LEFT OUTER JOIN sys.selective_xml_index_paths AS indexedpaths ON xi.object_id = indexedpaths.object_id AND xi.using_xml_index_id = indexedpaths.index_id AND xi.path_id = indexedpaths.path_id
	LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON i.object_id = filetableobj.object_id
	LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
	LEFT OUTER JOIN sys.stats AS s ON s.stats_id = i.index_id AND s.object_id = i.object_id
	LEFT OUTER JOIN sys.xml_indexes AS xi2 ON xi2.object_id = xi.object_id AND xi2.index_id = xi.using_xml_index_id
	LEFT OUTER JOIN sys.spatial_indexes AS spi ON i.object_id = spi.object_id and i.index_id = spi.index_id
WHERE
	(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Name] ASC
OPTION (FORCE ORDER)
GO

--- PRIMARY KEY EXTENDED PROPERTIES

declare 
	@_msparam_0 int = 0,
	@_msparam_1 bit = 0,
	@_msparam_2 nvarchar(max) = N'posts',
	@_msparam_3 nvarchar(max) = N'dbo'

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	i.name AS [Index_Name],
	p.name AS [Name],
	CAST(p.value AS sql_variant) AS [Value]
FROM
	sys.tables AS tbl
	INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
	LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
	INNER JOIN sys.extended_properties AS p ON 
		p.major_id = CASE (i.is_primary_key + 2*i.is_unique_constraint) WHEN 0 THEN i.object_id ELSE k.object_id END AND 
		p.minor_id = CASE (i.is_primary_key + 2*i.is_unique_constraint) WHEN 0 THEN CAST(i.index_id AS int) ELSE 0 END AND 
		p.class	   = CASE (i.is_primary_key + 2*i.is_unique_constraint) WHEN 0 THEN 7 ELSE 1 END
WHERE
	(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Index_Name] ASC,[Name] ASC
OPTION (FORCE ORDER)

GO
----- TABLE INDEXES

declare 
	@_msparam_0 int = 0,
	@_msparam_1 bit = 0,
	@_msparam_2 nvarchar(max) = N'posts',
	@_msparam_3 nvarchar(max) = N'dbo'

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [server_name],
	db_name() AS [database_name],
	SCHEMA_NAME(tbl.schema_id) AS [table_schema],
	tbl.name AS [table_name],
	i.name AS [index_name],
	(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [id],
	clmns.name AS [column_name],
	ic.is_included_column AS [is_included],
	ic.is_descending_key AS [is_descending],
	CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N'IsComputed') AS bit) AS [is_computed]
FROM
	sys.tables AS tbl
	INNER JOIN sys.indexes AS i 
		ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
	INNER JOIN sys.index_columns AS ic 
		ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
	INNER JOIN sys.columns AS clmns 
		ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
	tbl.[name] = @_msparam_2 and SCHEMA_NAME(tbl.schema_id) = @_msparam_3
ORDER BY
	[database_name] ASC,[table_schema] ASC,[table_name] ASC,[index_name] ASC,[id] ASC
OPTION (FORCE ORDER)

GO
---------- TABLE FULLTEXT SEARCH INDEX Catalogs and File groups

declare @_msparam_0 nvarchar(max) = 'posts',
		@_msparam_1 nvarchar(max) = 'dbo';

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [server_name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	tbl.name AS [Name],
	CAST(fti.is_enabled AS bit) AS [IsEnabled],
	si.name AS [UniqueIndexName],
	cat.name AS [CatalogName],
	(case change_tracking_state when 'M' then 1 when 'A' then 2 else 0 end) AS [ChangeTracking],
	fg.name AS [FilegroupName],
	ISNULL(sl.name,N'') AS [StopListName],
	(case when fti.stoplist_id is NULL then 0 when fti.stoplist_id = 0 then 1 else 2 end) AS [StopListOption],
	ISNULL(spl.name,N'') AS [SearchPropertyListName]
FROM
	sys.tables AS tbl
	INNER JOIN sys.fulltext_indexes AS fti ON fti.object_id=tbl.object_id
	INNER JOIN sys.indexes AS si ON si.index_id=fti.unique_index_id and si.object_id=fti.object_id
	INNER JOIN sys.fulltext_catalogs AS cat ON cat.fulltext_catalog_id = fti.fulltext_catalog_id
	INNER JOIN sys.filegroups AS fg ON fg.data_space_id = fti.data_space_id
	LEFT OUTER JOIN sys.fulltext_stoplists AS sl ON sl.stoplist_id = fti.stoplist_id
	LEFT OUTER JOIN sys.registered_search_property_lists AS spl ON spl.property_list_id = fti.property_list_id
WHERE
	(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC
OPTION (FORCE ORDER)
GO

---------- TABLE FULLTEXT SEARCH INDEXES

declare @_msparam_0 nvarchar(max) = 'posts',
		@_msparam_1 nvarchar(max) = 'dbo';

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	tbl.name AS [FullTextIndex_Name],
	col.name AS [Name],
	ISNULL(col2.name,N'') AS [TypeColumnName],
	sl.name AS [Language],
	icol.statistical_semantics AS [StatisticalSemantics]
	FROM
	sys.tables AS tbl
	INNER JOIN sys.fulltext_indexes AS fti ON fti.object_id=tbl.object_id
	INNER JOIN sys.fulltext_index_columns AS icol ON icol.object_id=fti.object_id
	INNER JOIN sys.columns AS col ON col.object_id = icol.object_id and col.column_id = icol.column_id
	LEFT OUTER JOIN sys.columns AS col2 ON col2.column_id = icol.type_column_id and col2.object_id = icol.object_id
	INNER JOIN sys.fulltext_languages AS sl ON sl.lcid=icol.language_id
WHERE
	(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Name] ASC
OPTION (FORCE ORDER)

GO

----- TABLE EXTENDED PROPERTIES
declare @_msparam_0 nvarchar(max) = 'posts',
		@_msparam_1 nvarchar(max) = 'dbo';

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	p.name AS [Name],
	CAST(p.value AS sql_variant) AS [Value]
FROM
	sys.tables AS tbl
	INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1
WHERE
	(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Name] ASC
OPTION (FORCE ORDER)

GO

--- TABLE FILETABLE

declare @_msparam_0 nvarchar(max) = 'posts',
		@_msparam_1 nvarchar(max) = 'dbo';
SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name], db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	cstr.name AS [Name],
	cstr.is_not_for_replication AS [NotForReplication],
	~cstr.is_not_trusted AS [IsChecked],
	~cstr.is_disabled AS [IsEnabled],
	CAST(cstr.is_system_named AS bit) AS [IsSystemNamed],
	CAST(CASE WHEN filetableobj.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS [IsFileTableDefined],
	cstr.definition AS [Text]
FROM
	sys.tables AS tbl
	INNER JOIN sys.check_constraints AS cstr ON cstr.parent_object_id=tbl.object_id
	LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON filetableobj.object_id = cstr.object_id
WHERE
	(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Name] ASC
OPTION (FORCE ORDER)

GO
------ CHECK CONSTRINT NAME
declare @_msparam_0 nvarchar(max) = 'posts',
		@_msparam_1 nvarchar(max) = 'dbo';

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	cstr.name AS [Check_Name],
	p.name AS [Name],
	CAST(p.value AS sql_variant) AS [Value]
FROM
	sys.tables AS tbl
	INNER JOIN sys.check_constraints AS cstr ON cstr.parent_object_id=tbl.object_id
	INNER JOIN sys.extended_properties AS p ON p.major_id=cstr.object_id AND p.minor_id=0 AND p.class=1
WHERE
	(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Check_Name] ASC,[Name] ASC
OPTION (FORCE ORDER)

GO
------- CHECK CONSTRAINT ACTIONS
declare @_msparam_0 nvarchar(max) = 'posts',
		@_msparam_1 nvarchar(max) = 'dbo';

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	cstr.name AS [Name],
	CAST(cstr.is_system_named AS bit) AS [IsSystemNamed],
	cstr.delete_referential_action AS [DeleteAction],
	cstr.update_referential_action AS [UpdateAction],
	rtbl.name AS [ReferencedTable],
	schema_name(rtbl.schema_id) AS [ReferencedTableSchema],
	~cstr.is_not_trusted AS [IsChecked],
	~cstr.is_disabled AS [IsEnabled],
	cstr.is_not_for_replication AS [NotForReplication],
	CAST(CASE WHEN filetableobj.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS [IsFileTableDefined]
FROM
	sys.tables AS tbl
	INNER JOIN sys.foreign_keys AS cstr ON cstr.parent_object_id=tbl.object_id
	INNER JOIN sys.tables rtbl ON rtbl.object_id = cstr.referenced_object_id
	LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON filetableobj.object_id = cstr.object_id
WHERE
	(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
	[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Name] ASC
OPTION (FORCE ORDER)

GO
---------------- CHECK CONSTRAINT FOREIGN KEY
declare @_msparam_0 nvarchar(max) = 'posts',
		@_msparam_1 nvarchar(max) = 'dbo';

SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	cstr.name AS [ForeignKey_Name],
	fk.constraint_column_id AS [ID],
	cfk.name AS [Name],
	crk.name AS [ReferencedColumn]
FROM
	sys.tables AS tbl
	INNER JOIN sys.foreign_keys AS cstr ON cstr.parent_object_id=tbl.object_id
	INNER JOIN sys.foreign_key_columns AS fk ON fk.constraint_object_id=cstr.object_id
	INNER JOIN sys.columns AS cfk ON fk.parent_column_id = cfk.column_id and fk.parent_object_id = cfk.object_id
	INNER JOIN sys.columns AS crk ON fk.referenced_column_id = crk.column_id and fk.referenced_object_id = crk.object_id
WHERE
(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[ForeignKey_Name] ASC,[ID] ASC
OPTION (FORCE ORDER)

GO

---- EXTENDED PROPERTIES
declare @_msparam_0 nvarchar(max) = 'posts',
		@_msparam_1 nvarchar(max) = 'dbo';


SELECT
	CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
	db_name() AS [Database_Name],
	SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
	tbl.name AS [Table_Name],
	cstr.name AS [ForeignKey_Name],
	p.name AS [Name],
	CAST(p.value AS sql_variant) AS [Value]
FROM
	sys.tables AS tbl
	INNER JOIN sys.foreign_keys AS cstr ON cstr.parent_object_id=tbl.object_id
	INNER JOIN sys.extended_properties AS p ON p.major_id=cstr.object_id AND p.minor_id=0 AND p.class=1
WHERE
	(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
	[Database_Name] ASC, [Table_Schema] ASC, [Table_Name] ASC, [ForeignKey_Name] ASC, [Name] ASC
OPTION (FORCE ORDER)

GO
/*
	Database configuration

	To simplify CREATE query, we need to know what are database defaults.
	Below are queries that should provide info for such configuration
*/
declare @UserOption int

select @UserOption=convert(int, c.value) 
from sys.configurations c where c.name='user options'

SELECT
	CAST(@UserOption & 1 AS bit) AS [DisableDefaultConstraintCheck],
	CAST(@UserOption & 2 AS bit) AS [ImplicitTransactions],
	CAST(@UserOption & 4 AS bit) AS [CursorCloseOnCommit],
	CAST(@UserOption & 8 AS bit) AS [AnsiWarnings],
	CAST(@UserOption & 16 AS bit) AS [AnsiPadding],
	CAST(@UserOption & 32 AS bit) AS [AnsiNulls],
	CAST(@UserOption & 64 AS bit) AS [AbortOnArithmeticErrors],
	CAST(@UserOption & 128 AS bit) AS [IgnoreArithmeticErrors],
	CAST(@UserOption & 256 AS bit) AS [QuotedIdentifier],
	CAST(@UserOption & 512 AS bit) AS [NoCount],
	CAST(@UserOption & 1024 AS bit) AS [AnsiNullDefaultOn],
	CAST(@UserOption & 2048 AS bit) AS [AnsiNullDefaultOff],
	CAST(@UserOption & 4096 AS bit) AS [ConcatenateNullYieldsNull],
	CAST(@UserOption & 8192 AS bit) AS [NumericRoundAbort],
	CAST(@UserOption & 16384 AS bit) AS [AbortTransactionOnError]
GO

---

declare @_msparam_0 nvarchar(max) = N'ecto_test'

SELECT
	dtb.is_ansi_null_default_on AS [AnsiNullDefault],
	dtb.is_ansi_nulls_on AS [AnsiNullsEnabled],
	dtb.is_ansi_padding_on AS [AnsiPaddingEnabled],
	dtb.is_ansi_warnings_on AS [AnsiWarningsEnabled],
	dtb.is_arithabort_on AS [ArithmeticAbortEnabled],
	dtb.is_auto_shrink_on AS [AutoShrink],
	dtb.is_cursor_close_on_commit_on AS [CloseCursorsOnCommitEnabled],
	dtb.is_concat_null_yields_null_on AS [ConcatenateNullYieldsNull],
	dtb.is_numeric_roundabort_on AS [NumericRoundAbortEnabled],
	dtb.is_quoted_identifier_on AS [QuotedIdentifiersEnabled],
	dtb.is_read_only AS [ReadOnly],
	dtb.is_recursive_triggers_on AS [RecursiveTriggersEnabled],
	dtb.user_access AS [UserAccess],
	dtb.is_db_chaining_on AS [DatabaseOwnershipChaining],
	dtb.is_auto_update_stats_async_on AS [AutoUpdateStatisticsAsync],
	dtb.is_date_correlation_on AS [DateCorrelationOptimization],
	dtb.is_trustworthy_on AS [Trustworthy],
	dtb.name AS [Name],
	dtb.database_id AS [ID],
	dtb.create_date AS [CreateDate],
	dtb.is_auto_create_stats_on AS [AutoCreateStatisticsEnabled],
	dtb.is_auto_update_stats_on AS [AutoUpdateStatisticsEnabled],
	dtb.is_parameterization_forced AS [IsParameterizationForced],
	dtb.is_read_committed_snapshot_on AS [IsReadCommittedSnapshotOn],
	CAST(isnull(dtb.source_database_id, 0) AS bit) AS [IsDatabaseSnapshot],
	ISNULL(DB_NAME(dtb.source_database_id), N'') AS [DatabaseSnapshotBaseName],
	dtb.is_fulltext_enabled AS [IsFullTextEnabled],
	dtb.service_broker_guid AS [ServiceBrokerGuid],
	dtb.snapshot_isolation_state AS [SnapshotIsolationState],
	(dtb.is_published*1+dtb.is_subscribed*2+dtb.is_merge_published*4) AS [ReplicationOptions],
	dtb.is_local_cursor_default AS [LocalCursorsDefault],
	dtb.page_verify_option AS [PageVerify],
	dtb.recovery_model AS [RecoveryModel],
	dtb.is_auto_close_on AS [AutoClose],
	dtb.is_broker_enabled AS [BrokerEnabled],
	ISNULL(suser_sname(dtb.owner_sid),'') AS [Owner],
	ISNULL(dtb.log_reuse_wait,0) AS [LogReuseWaitStatus],
	drs.recovery_fork_guid AS [RecoveryForkGuid],
	drs.database_guid AS [DatabaseGuid],
	CAST((case when drs.last_log_backup_lsn is not null then 1 else 0 end) AS bit) AS [HasFullBackup],
	CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject],
	CAST(case when ctb.database_id is null then 0 else 1  end AS bit) AS [ChangeTrackingEnabled],
	CAST(ISNULL(ctb.is_auto_cleanup_on,0) AS bit) AS [ChangeTrackingAutoCleanUp],
	ISNULL(ctb.retention_period,0) AS [ChangeTrackingRetentionPeriod],
	CAST(ISNULL(ctb.retention_period_units,0) AS tinyint) AS [ChangeTrackingRetentionPeriodUnits],
	dtb.containment AS [ContainmentType],
	dtb.default_language_lcid AS [DefaultLanguageLcid],
	dtb.default_language_name AS [DefaultLanguageName],
	dtb.default_fulltext_language_lcid AS [DefaultFullTextLanguageLcid],
	ISNULL(dtb.default_fulltext_language_name,N'') AS [DefaultFullTextLanguageName],
	CAST(dtb.is_nested_triggers_on AS bit) AS [NestedTriggersEnabled],
	CAST(dtb.is_transform_noise_words_on AS bit) AS [TransformNoiseWords],
	dtb.two_digit_year_cutoff AS [TwoDigitYearCutoff],
	dtb.target_recovery_time_in_seconds AS [TargetRecoveryTime],
	dtb.delayed_durability AS [DelayedDurability],
	dtb.is_auto_create_stats_incremental_on AS [AutoCreateIncrementalStatisticsEnabled],
	case
        when dtb.collation_name is null then 0x200
        else 0
        end |
        case
        when 1 = dtb.is_in_standby then 0x40
        else 0
        end |
        case dtb.state
        when 1 then 0x2
        when 2 then 0x8
        when 3 then 0x4
        when 4 then 0x10
        when 5 then 0x100
        when 6 then 0x20
        else 1
        end
       AS [Status],
	CAST(( case LOWER(convert( nvarchar(128), DATABASEPROPERTYEX(dtb.name, 'Updateability'))) when 'read_write' then 1 else 0 end) AS bit) AS [IsUpdateable],
	CAST(dtb.is_encrypted AS bit) AS [EncryptionEnabled],
	CAST(dtb.is_honor_broker_priority_on AS bit) AS [HonorBrokerPriority],
	CAST(
        case
        when SERVERPROPERTY('EngineEdition') = 6 then cast(1 as bit)
        else cast(0 as bit)
        end
       AS bit) AS [IsSqlDw],
	CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible],
	ISNULL(fsopt.directory_name , N'') AS [FilestreamDirectoryName],
	ISNULL(fsopt.non_transacted_access , 0) AS [FilestreamNonTransactedAccess],
	CAST(dtb.is_remote_data_archive_enabled AS bit) AS [RemoteDataArchiveEnabled],
	0 AS [MaxDop],
	NULL AS [MaxDopForSecondary],
	0 AS [LegacyCardinalityEstimation],
	2 AS [LegacyCardinalityEstimationForSecondary],
	0 AS [ParameterSniffing],
	2 AS [ParameterSniffingForSecondary],
	0 AS [QueryOptimizerHotfixes],
	2 AS [QueryOptimizerHotfixesForSecondary],
	CAST(ISNULL(DATABASEPROPERTYEX(dtb.name, 'lastgoodcheckdbtime'), 0) AS datetime) AS [LastGoodCheckDbTime],
	dtb.name AS [DatabaseName2],
	dtb.containment AS [ContainmentType2],
	dtb.name AS [DatabaseName6]
FROM
	master.sys.databases AS dtb
	LEFT OUTER JOIN sys.database_recovery_status AS drs ON drs.database_id = dtb.database_id
	LEFT OUTER JOIN sys.change_tracking_databases AS ctb ON ctb.database_id = dtb.database_id 
	LEFT OUTER JOIN sys.database_filestream_options AS fsopt ON fsopt.database_id = dtb.database_id
WHERE
	(dtb.name=@_msparam_0)


-----------

select 
case 
    when cfg.configuration_id = 124 -- configuration id for default language
    then (select lcid from sys.syslanguages as sl where sl.langid = cfg.value_in_use) -- getting default language LCID from default language langid
    else cfg.value_in_use
end as [value],
case 
    when cfg.configuration_id = 124 -- configuration id for default language
    then (select name collate catalog_default from sys.syslanguages as sl where sl.langid = cfg.value_in_use) -- getting default language name from default language langid
    when cfg.configuration_id = 1126 -- configuration id for default fulltext language
    then ISNULL((select name collate catalog_default from sys.fulltext_languages as fl where fl.lcid = cfg.value_in_use), N'') -- getting default fulltext language name from default fulltext language lcid
    else null
end as [name],
cfg.configuration_id as configuration_id
from sys.configurations as cfg
where cfg.configuration_id in (115, 124, 1126, 1127, 1555) 
order by cfg.configuration_id asc

----

SELECT CASE WHEN has_dbaccess(N'ecto_test') = 1 THEN 'true' ELSE 'false' END

----

declare @UserOption int
select @UserOption=convert(int, c.value) 
from sys.configurations c where c.name='user options'
      


SELECT
	CAST(@UserOption & 1 AS bit) AS [DisableDefaultConstraintCheck],
	CAST(@UserOption & 2 AS bit) AS [ImplicitTransactions],
	CAST(@UserOption & 4 AS bit) AS [CursorCloseOnCommit],
	CAST(@UserOption & 8 AS bit) AS [AnsiWarnings],
	CAST(@UserOption & 16 AS bit) AS [AnsiPadding],
	CAST(@UserOption & 32 AS bit) AS [AnsiNulls],
	CAST(@UserOption & 64 AS bit) AS [AbortOnArithmeticErrors],
	CAST(@UserOption & 128 AS bit) AS [IgnoreArithmeticErrors],
	CAST(@UserOption & 256 AS bit) AS [QuotedIdentifier],
	CAST(@UserOption & 512 AS bit) AS [NoCount],
	CAST(@UserOption & 1024 AS bit) AS [AnsiNullDefaultOn],
	CAST(@UserOption & 2048 AS bit) AS [AnsiNullDefaultOff],
	CAST(@UserOption & 4096 AS bit) AS [ConcatenateNullYieldsNull],
	CAST(@UserOption & 8192 AS bit) AS [NumericRoundAbort],
	CAST(@UserOption & 16384 AS bit) AS [AbortTransactionOnError]