System Versioned Table check schema from sys.tables-CodePudding

How to check if a table has versioning enabled by specifying not only the table name but also the schema.

I use:

IF EXISTS (SELECT 1 FROM sys.tables 
           WHERE NAME = 'tablename' 
             AND temporal_type = 2) 
    SELECT 1 AS TABLE_EXIST 
ELSE 
    SELECT 0 AS TABLE_EXIST

Regards

CodePudding user response：

You can use the SCHEMA_ID function, which exists to simplify queries like this:

SELECT  *
FROM SYS.tables 
WHERE 
NAME = 'tablename' 
and schema_id = schema_id('dbo')
and temporal_type = 2

CodePudding user response：

You can JOIN sys.schemas and filter on the name column from both tables:

IF EXISTS (
    SELECT 1
    FROM sys.tables t
    INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE temporal_type = 2
    AND s.name = 'schemaname'
    AND t.name = 'tablename'
)
    SELECT 1 AS TABLE_EXIST
ELSE
    SELECT 0 AS TABLE_EXIST;