Limitations – Manual Assessment Scripts

(1) Determine SQL Server version 
- CDC is supported in Standard and Enterprise editions.
- List of tables result set, indicating whether tables have PKs, Unique Indexes, is expected. 

SELECT @@version;

(1) Determine Azure SQL Server version and tier (CDC is supported in Standard S3 and above)

SELECT @@version;
SELECT * FROM sys.database_service_objectives

(2) List tables indicating the most common limitations-related properties
 
SELECT DISTINCT
    t.object_id
	,schema_name(schema_id) as table_schema
	,t.name as table_name
    ,p.rows AS row_counts
	, OBJECTPROPERTY(t.object_id,'TableHasPrimaryKey') as has_primary_key
	, (
		CASE WHEN (SELECT COUNT(*) FROM sys.indexes ix WITH (NOLOCK) WHERE t.object_id=ix.object_id AND ix.is_unique=1) > 0 THEN 1 ELSE 0 END
	) as has_unique_index
	, (
		CASE WHEN (SELECT COUNT(*) FROM sys.indexes ix WITH (NOLOCK) WHERE t.object_id=ix.object_id AND ix.type IN(5,6) ) > 0 THEN 1 ELSE 0 END
	) as has_column_store_index
FROM sys.tables t WITH (NOLOCK)
INNER JOIN sys.indexes i WITH (NOLOCK) ON t.object_id = i.object_id
INNER JOIN sys.partitions p WITH (NOLOCK) ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a WITH (NOLOCK) ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0 AND t.NAME NOT IN ('sysdiagrams','dtproperties')

(3) List tables with timestamp Primary Keys (PKs)
 
SELECT DISTINCT
	schema_name(t.schema_id) table_schema,
	t.name as table_name
FROM sys.tables t WITH (NOLOCK)
INNER JOIN sys.indexes pk WITH (NOLOCK) ON t.object_id = pk.object_id AND pk.is_primary_key = 1
INNER JOIN sys.index_columns ic WITH (NOLOCK) ON ic.object_id = pk.object_id AND ic.index_id = pk.index_id
INNER JOIN sys.columns col WITH (NOLOCK) ON pk.object_id = col.object_id AND col.column_id = ic.column_id
INNER JOIN sys.types y WITH (NOLOCK) ON y.user_type_id = col.user_type_id
WHERE y.name = 'timestamp'

(4) List tables with timestamp columns (any column, not only timestamp PKs)
 
SELECT DISTINCT
	schema_name(t.schema_id) table_schema,
	t.name as table_name
FROM sys.tables t WITH (NOLOCK)
INNER JOIN sys.columns col WITH (NOLOCK) ON t.object_id = col.object_id
INNER JOIN sys.types y WITH (NOLOCK) ON y.user_type_id = col.user_type_id
WHERE y.name = 'timestamp'