The following example utilizes the various GET DDL procedures to generate the entire inuser schema.
Note: For the sake of completeness, please note that the cumulative
output of the following example does not include all schema objects. It does not include
the IN_MESSAGE_SEQUENCE sequence or the INEMUSER schema objects.
The following example utilizes the synonyms for the procedures rather than their full names.
EXEC IN_GET_TABLE_DDL; EXEC IN_GET_PK_DDL; EXEC IN_GET_INDEX_DDL; EXEC IN_GET_FK_DDL; GO
The following example utilizes the various GET DDL procedures to generate a portion of the inuser schema based on the query used get the table names. In this case, the audit tables.
DECLARE @TABLE SYSNAME, @PASS INTEGER = 4; DECLARE GET_TABLES CURSOR LOCAL READ_ONLY FOR SELECT NAME FROM SYS.TABLES WHERE SCHEMA_ID = SCHEMA_ID('INUSER') AND NAME LIKE 'IN_AUDIT%' ORDER BY NAME; WHILE (@PASS > 0) BEGIN IF @PASS = 4 PRINT '-- ################## TABLES ##################'+CHAR(10)+CHAR(10); IF @PASS = 3 PRINT '-- ################## PRIMARY KEYS ##################'+CHAR(10)+CHAR(10); IF @PASS = 2 PRINT '-- ################## INDEXES ##################'+CHAR(10)+CHAR(10); IF @PASS = 1 PRINT '-- ################## FOREIGN KEYS ##################'+CHAR(10)+CHAR(10); OPEN GET_TABLES; FETCH NEXT FROM GET_TABLES INTO @TABLE; WHILE @@FETCH_STATUS = 0 BEGIN IF @PASS = 4 EXEC IN_GET_TABLE_DDL @TABLE; IF @PASS = 3 EXEC IN_GET_PK_DDL @TABLE; IF @PASS = 2 EXEC IN_GET_INDEX_DDL @TABLE; IF @PASS = 1 EXEC IN_GET_FK_DDL @TABLE; FETCH NEXT FROM GET_TABLES INTO @TABLE; END; SET @PASS += -1; CLOSE GET_TABLES; END; DEALLOCATE GET_TABLES; GO