Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORAenumpublishertables

  No additional text.


Syntax

CREATE PROCEDURE sys.sp_ORAenumpublishertables
(
    @publisher sysname
)
AS
BEGIN
    DECLARE @publisher_dbms     sysname
    DECLARE @publisher_version  sysname
    DECLARE @InsColumnList      nvarchar(2000)
    DECLARE @SelectColumnList   nvarchar(2000)
    DECLARE @retcode            int
    DECLARE @pubid              int

    SET NOCOUNT ON

    -- Get publisher ID
    SELECT @pubid = srvid
    FROM    master.dbo.sysservers
    WHERE   UPPER(srvname) = UPPER(@publisher)

    IF @pubid IS NULL
    BEGIN
        RAISERROR(21600, 16, -1, @publisher)
        RETURN (1)
    END

	-- Set publisher DBMS and version
	SELECT	@publisher_dbms		= 'ORACLE'

	EXEC @retcode = sys.sp_IHgetversion	@publisher	= @publisher,
										@version	= @publisher_version OUTPUT

	IF @retcode != 0 OR @@ERROR != 0
	BEGIN
		RETURN (1)
	END

	-- Define sp_ORAremotequery support table
	create table #hquery
	(
		seq	int identity(2,1),
		cmd	nvarchar(4000)
	)

	CREATE TABLE #oratables
	(
		table_owner	sysname collate database_default,
		table_name	sysname collate database_default,
		has_pk      bit
	)
	
	-- Master table list
	SELECT @InsColumnList    = 'table_owner, table_name, has_pk'
	SELECT @SelectColumnList = '*'

    INSERT INTO #hquery(cmd) VALUES('SELECT OWNER, TABLE_NAME, DECODE(COUNT(*), 1, 0, 2, 1) ')
    INSERT INTO #hquery(cmd) VALUES('FROM (SELECT T.OWNER, T.TABLE_NAME FROM ALL_ALL_TABLES T ')
    INSERT INTO #hquery(cmd) VALUES('WHERE T.NESTED = ''NO'' AND T.TABLE_NAME NOT LIKE ''HREPL_%'' ')
    INSERT INTO #hquery(cmd) VALUES('AND T.OWNER NOT IN (SELECT * FROM HREPL_SchemaFilter) ')
    INSERT INTO #hquery(cmd) VALUES('AND NOT ( T.OWNER = USER ) ')

    IF @publisher_version NOT LIKE N'8.%' AND @publisher_version NOT LIKE N'9.%'
	INSERT INTO #hquery(cmd) VALUES('AND T.DROPPED = ''NO'' ')

    IF @publisher_version NOT LIKE N'8.%'
	BEGIN
		INSERT INTO #hquery(cmd) VALUES('UNION ALL ')
		INSERT INTO #hquery(cmd) VALUES('SELECT AC.OWNER, AC.TABLE_NAME ')
		INSERT INTO #hquery(cmd) VALUES('FROM ALL_CONSTRAINTS AC, ALL_INDEXES IDX')

		-- If @publisher_version is neither 8 nor 9, make sure that the table
		-- has not been dropped
		IF @publisher_version NOT LIKE N'9.%'
		    INSERT INTO #hquery(cmd) VALUES(', ALL_ALL_TABLES T')

		-- Primary key with valid index
		INSERT INTO #hquery(cmd) VALUES(' WHERE CASE WHEN AC.INDEX_OWNER IS NOT NULL THEN AC.INDEX_OWNER ELSE AC.OWNER END = IDX.OWNER ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.INDEX_NAME = IDX.INDEX_NAME ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.CONSTRAINT_TYPE = ''P'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.VALIDATED = ''VALIDATED'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.STATUS = ''ENABLED'' ')
		INSERT INTO #hquery(cmd) VALUES('AND IDX.STATUS = ''VALID'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME NOT LIKE ''HREPL_%'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.OWNER NOT IN (SELECT * FROM HREPL_SchemaFilter) ')
		INSERT INTO #hquery(cmd) VALUES('AND NOT ( AC.OWNER = USER ) ')

		IF @publisher_version NOT LIKE N'9.%'
		BEGIN
		    INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME = T.TABLE_NAME ')
		    INSERT INTO #hquery(cmd) VALUES('AND CASE WHEN AC.INDEX_OWNER IS NOT NULL THEN AC.INDEX_OWNER ELSE AC.OWNER END = T.OWNER ')
		    INSERT INTO #hquery(cmd) VALUES('AND T.DROPPED = ''NO'' ')
		END
	END
	ELSE
	BEGIN
		-- Primary key index is no longer being checked for Oracle 8.
		-- The Oracle 8 version of all_constraints does not explicitly identify
		-- the name of the primary key index, and since the constraint name and
		-- index name need not be the same, this check has been dropped.
		-- This is fairly safe, since the index cannot be dropped while it is
		-- being used to enforce a primary key constraint.

		INSERT INTO #hquery(cmd) VALUES('UNION ALL ')
		INSERT INTO #hquery(cmd) VALUES('SELECT AC.OWNER, AC.TABLE_NAME ')
		INSERT INTO #hquery(cmd) VALUES('FROM ALL_CONSTRAINTS AC ')
		INSERT INTO #hquery(cmd) VALUES('WHERE AC.CONSTRAINT_TYPE = ''P'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.VALIDATED = ''VALIDATED'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.STATUS = ''ENABLED'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME NOT LIKE ''HREPL_%'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.OWNER NOT IN (SELECT * FROM HREPL_SchemaFilter) ')
		INSERT INTO #hquery(cmd) VALUES('AND NOT ( AC.OWNER = USER ) ')
	END
    INSERT INTO #hquery(cmd) VALUES(') GROUP BY OWNER, TABLE_NAME')

    EXEC @retcode = sp_ORAremotequery
					@Server				= @publisher,
					@SelectColumnList	= @SelectColumnList,
					@InsTable			= '#oratables',
					@InsColumnList		= @InsColumnList

	IF @@ERROR <> 0 OR @retcode <> 0
	BEGIN
		RAISERROR (21781, 16, -1)
		RETURN (@retcode)
	END

	-- Generate return set:
	INSERT INTO #publishertables
			-- owner
	SELECT	OT.table_owner,
			-- name
			OT.table_name,
			-- has_valid_pk
            OT.has_pk,
            -- TODO: Remove after UI removes references
            -- has_unique_with_nullable
            CONVERT(bit, 0),
			-- is_published
			CONVERT(bit, CASE WHEN (ihpt.table_id IS NOT NULL) THEN 1 ELSE 0 END),
            -- TODO: Remove after UI removes references
			-- has_explicit_select_grant
            CONVERT(bit, 1),
            -- is_tranpublished
			CONVERT(bit, CASE
					WHEN EXISTS (
							SELECT	NULL
							FROM	dbo.IHarticles iha with (index (idx_IHarticles_tableid) ),
								dbo.IHpublications ihpub with (index (idx_IHpublications_pubid) )
        						WHERE   iha.table_id = ihpt.table_id
							AND	iha.publication_id = ihpub.pubid
         						AND     ihpub.repl_freq = 0)
     					THEN 1 ELSE 0 END)
	FROM	#oratables OT LEFT OUTER JOIN dbo.IHpublishertables ihpt
			ON	OT.table_owner = ihpt.owner
			AND	OT.table_name = ihpt.name
			AND	ihpt.publisher_id = @pubid
	ORDER BY [owner], [name]

	RETURN (0)
END

 
Last revision 2008RTM
See also

  sp_MSdrop_replcom (Procedure)
sp_MSrepl_enumpublishertables (Procedure)
sp_ORASchemaFilter (Procedure)
sp_schemafilter (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash