Skip to content

Commit e543682

Browse files
ktaranovktaranov
committed
Add Table_Parameters script
1 parent 024b977 commit e543682

1 file changed

Lines changed: 152 additions & 0 deletions

File tree

‎Scripts/Table_Parameters.sql‎

Lines changed: 152 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,152 @@
1+
/*
2+
Author: SSMS
3+
*/
4+
5+
exec sp_executesql N'
6+
CREATE TABLE #tmp_extended_remote_data_archive_tables
7+
(object_id int not null, remote_table_name nvarchar(128) null, filter_predicate nvarchar(max) null, migration_state tinyint null)
8+
9+
IF EXISTS(SELECT 1 FROM master.sys.syscolumns WHERE Name = N''remote_data_archive_migration_state'' AND ID = Object_ID(N''sys.tables''))
10+
EXECUTE(N''INSERT INTO #tmp_extended_remote_data_archive_tables SELECT rdat.object_id, rdat.remote_table_name,
11+
SUBSTRING(rdat.filter_predicate, 2, LEN(rdat.filter_predicate) - 2) as filter_predicate,
12+
CASE
13+
WHEN tbl.remote_data_archive_migration_state_desc = N''''PAUSED'''' THEN 1
14+
WHEN tbl.remote_data_archive_migration_state_desc = N''''OUTBOUND'''' THEN 3
15+
WHEN tbl.remote_data_archive_migration_state_desc = N''''INBOUND'''' THEN 4
16+
WHEN tbl.remote_data_archive_migration_state_desc = N''''DISABLED'''' THEN 0
17+
ELSE 0
18+
END AS migration_state
19+
FROM sys.tables tbl LEFT JOIN sys.remote_data_archive_tables rdat ON rdat.object_id = tbl.object_id
20+
WHERE rdat.object_id IS NOT NULL'')
21+
ELSE
22+
EXECUTE(N''INSERT INTO #tmp_extended_remote_data_archive_tables SELECT rdat.object_id, rdat.remote_table_name,
23+
SUBSTRING(rdat.filter_predicate, 2, LEN(rdat.filter_predicate) - 2) as filter_predicate,
24+
CASE
25+
WHEN rdat.is_migration_paused = 1 AND rdat.migration_direction_desc = N''''OUTBOUND'''' THEN 1
26+
WHEN rdat.is_migration_paused = 1 AND rdat.migration_direction_desc = N''''INBOUND'''' THEN 2
27+
WHEN rdat.is_migration_paused = 0 AND rdat.migration_direction_desc = N''''OUTBOUND'''' THEN 3
28+
WHEN rdat.is_migration_paused = 0 AND rdat.migration_direction_desc = N''''INBOUND'''' THEN 4
29+
ELSE 0
30+
END AS migration_state
31+
FROM sys.tables tbl LEFT JOIN sys.remote_data_archive_tables rdat ON rdat.object_id = tbl.object_id
32+
WHERE rdat.object_id IS NOT NULL'')
33+
34+
35+
36+
SELECT
37+
tbl.name AS [Name],
38+
tbl.object_id AS [ID],
39+
tbl.create_date AS [CreateDate],
40+
tbl.modify_date AS [DateLastModified],
41+
ISNULL(stbl.name, N'''') AS [Owner],
42+
CAST(case when tbl.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],
43+
SCHEMA_NAME(tbl.schema_id) AS [Schema],
44+
CAST(
45+
case
46+
when tbl.is_ms_shipped = 1 then 1
47+
when (
48+
select
49+
major_id
50+
from
51+
sys.extended_properties
52+
where
53+
major_id = tbl.object_id and
54+
minor_id = 0 and
55+
class = 1 and
56+
name = N''microsoft_database_tools_support'')
57+
is not null then 1
58+
else 0
59+
end
60+
AS bit) AS [IsSystemObject],
61+
CAST(OBJECTPROPERTY(tbl.object_id, N''HasAfterTrigger'') AS bit) AS [HasAfterTrigger],
62+
CAST(OBJECTPROPERTY(tbl.object_id, N''HasInsertTrigger'') AS bit) AS [HasInsertTrigger],
63+
CAST(OBJECTPROPERTY(tbl.object_id, N''HasDeleteTrigger'') AS bit) AS [HasDeleteTrigger],
64+
CAST(OBJECTPROPERTY(tbl.object_id, N''HasInsteadOfTrigger'') AS bit) AS [HasInsteadOfTrigger],
65+
CAST(OBJECTPROPERTY(tbl.object_id, N''HasUpdateTrigger'') AS bit) AS [HasUpdateTrigger],
66+
CAST(OBJECTPROPERTY(tbl.object_id, N''IsIndexed'') AS bit) AS [HasIndex],
67+
CAST(OBJECTPROPERTY(tbl.object_id, N''IsIndexable'') AS bit) AS [IsIndexable],
68+
CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusteredIndex],
69+
CAST(CASE idx.type WHEN 0 THEN 1 ELSE 0 END AS bit) AS [HasHeapIndex],
70+
tbl.uses_ansi_nulls AS [AnsiNullsStatus],
71+
CAST(ISNULL(OBJECTPROPERTY(tbl.object_id,N''IsQuotedIdentOn''),0) AS bit) AS [QuotedIdentifierStatus],
72+
CAST(0 AS bit) AS [FakeSystemTable],
73+
ISNULL(dstext.name,N'''') AS [TextFileGroup],
74+
CAST(tbl.is_memory_optimized AS bit) AS [IsMemoryOptimized],
75+
case when (tbl.durability=1) then 0 else 1 end AS [Durability],
76+
tbl.is_replicated AS [Replicated],
77+
tbl.lock_escalation AS [LockEscalation],
78+
CAST(case when ctt.object_id is null then 0 else 1 end AS bit) AS [ChangeTrackingEnabled],
79+
CAST(ISNULL(ctt.is_track_columns_updated_on,0) AS bit) AS [TrackColumnsUpdatedEnabled],
80+
tbl.is_filetable AS [IsFileTable],
81+
ISNULL(ft.directory_name,N'''') AS [FileTableDirectoryName],
82+
ISNULL(ft.filename_collation_name,N'''') AS [FileTableNameColumnCollation],
83+
CAST(ISNULL(ft.is_enabled,0) AS bit) AS [FileTableNamespaceEnabled],
84+
CASE WHEN ''PS''=dsidx.type THEN dsidx.name ELSE N'''' END AS [PartitionScheme],
85+
CAST(CASE WHEN ''PS''=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],
86+
CASE WHEN ''FD''=dstbl.type THEN dstbl.name ELSE N'''' END AS [FileStreamFileGroup],
87+
CASE WHEN ''PS''=dstbl.type THEN dstbl.name ELSE N'''' END AS [FileStreamPartitionScheme],
88+
CAST(CASE idx.type WHEN 5 THEN 1 ELSE 0 END AS bit) AS [HasClusteredColumnStoreIndex],
89+
CAST(CASE tbl.temporal_type WHEN 2 THEN 1 ELSE 0 END AS bit) AS [IsSystemVersioned],
90+
CAST(ISNULL(historyTable.name, N'''') AS sysname) AS [HistoryTableName],
91+
CAST(ISNULL(SCHEMA_NAME(historyTable.schema_id), N'''') AS sysname) AS [HistoryTableSchema],
92+
CAST(ISNULL(historyTable.object_id, 0) AS int) AS [HistoryTableID],
93+
CAST(CASE WHEN periods.start_column_id IS NULL THEN 0 ELSE 1 END AS bit) AS [HasSystemTimePeriod],
94+
CAST(
95+
ISNULL((SELECT cols.name
96+
FROM sys.columns cols
97+
WHERE periods.object_id = tbl.object_id
98+
AND cols.object_id = tbl.object_id
99+
AND cols.column_id = periods.start_column_id), N'''')
100+
AS sysname) AS [SystemTimePeriodStartColumn],
101+
CAST(
102+
ISNULL((SELECT cols.name
103+
FROM sys.columns cols
104+
WHERE periods.object_id = tbl.object_id
105+
AND cols.object_id = tbl.object_id
106+
AND cols.column_id = periods.end_column_id), N'''')
107+
AS sysname) AS [SystemTimePeriodEndColumn],
108+
tbl.temporal_type AS [TemporalType],
109+
CAST(tbl.is_remote_data_archive_enabled AS bit) AS [RemoteDataArchiveEnabled],
110+
CAST(
111+
ISNULL(rdat.migration_state, 0)
112+
AS tinyint) AS [RemoteDataArchiveDataMigrationState],
113+
CAST(rdat.filter_predicate AS varchar(4000)) AS [RemoteDataArchiveFilterPredicate],
114+
CAST(rdat.remote_table_name AS sysname) AS [RemoteTableName],
115+
CAST(CASE WHEN rdat.remote_table_name IS NULL THEN 0 ELSE 1 END AS bit) AS [RemoteTableProvisioned],
116+
CAST(tbl.is_external AS bit) AS [IsExternal],
117+
eds.name AS [DataSourceName],
118+
ISNULL(eff.name,N'''') AS [FileFormatName],
119+
ISNULL(et.location,N'''') AS [Location],
120+
121+
CASE et.reject_type
122+
WHEN ''VALUE'' THEN 0
123+
WHEN ''PERCENTAGE'' THEN 1
124+
ELSE -1
125+
END
126+
AS [RejectType],
127+
ISNULL(et.reject_value,0) AS [RejectValue],
128+
ISNULL(et.reject_sample_value,-1) AS [RejectSampleValue]
129+
FROM
130+
sys.tables AS tbl
131+
LEFT OUTER JOIN sys.periods as periods ON periods.object_id = tbl.object_id
132+
LEFT OUTER JOIN sys.tables as historyTable ON historyTable.object_id = tbl.history_table_id
133+
LEFT OUTER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, ''OwnerId'')))
134+
INNER JOIN sys.indexes AS idx ON
135+
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)))
136+
137+
LEFT OUTER JOIN sys.data_spaces AS dstext ON tbl.lob_data_space_id = dstext.data_space_id
138+
LEFT OUTER JOIN sys.change_tracking_tables AS ctt ON ctt.object_id = tbl.object_id
139+
LEFT OUTER JOIN sys.filetables AS ft ON ft.object_id = tbl.object_id
140+
LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id
141+
LEFT OUTER JOIN sys.tables AS t ON t.object_id = idx.object_id
142+
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))
143+
LEFT OUTER JOIN #tmp_extended_remote_data_archive_tables AS rdat ON rdat.object_id = tbl.object_id
144+
LEFT OUTER JOIN sys.external_tables AS et ON et.object_id = tbl.object_id
145+
LEFT OUTER JOIN sys.external_data_sources AS eds ON eds.data_source_id = et.data_source_id
146+
LEFT OUTER JOIN sys.external_file_formats AS eff ON eff.file_format_id = et.file_format_id
147+
WHERE
148+
(tbl.name=@_msparam_1 and SCHEMA_NAME(tbl.schema_id)=@_msparam_2)
149+
150+
DROP TABLE #tmp_extended_remote_data_archive_tables
151+
152+
',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'2',@_msparam_1=N'Node',@_msparam_2=N'dictionary';

0 commit comments

Comments
 (0)