Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORAenumtablecolumninfo

  No additional text.


Syntax

CREATE PROCEDURE sys.sp_ORAenumtablecolumninfo
(
	@publisher	sysname,
	@owner		sysname,
	@tablename	sysname
)
AS
BEGIN
    DECLARE @publisher_dbms		sysname
    DECLARE @publisher_version	sysname
	DECLARE	@InsColumnList		nvarchar(2000)
	DECLARE	@SelectColumnList	nvarchar(2000)
    DECLARE @retcode			int

	SET NOCOUNT ON

	-- 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)
	)

	-- Define temp metadata table
  	CREATE TABLE #publishercolumns
    (
		name			sysname collate database_default,
		column_ordinal	int,
		type			sysname collate database_default,
		length			bigint,
		prec			bigint,
		scale			int,
		isnullable		bit,
		ispkcol			bit
	)

    CREATE TABLE #publisherpkcolumns
    (
        name  sysname collate database_default
    )
	
	-- populate a temp table with a list of table columns from the Oracle publisher
	SELECT @InsColumnList    = 'name, column_ordinal, type, length, prec, scale, isnullable, ispkcol'
	SELECT @SelectColumnList = 'COLUMN_NAME, COLUMN_ID, DATA_TYPE, DATA_LENGTH, PREC, DATA_SCALE, NULLABLE, 0'

	INSERT INTO #hquery(cmd) VALUES('SELECT ATC.COLUMN_NAME AS COLUMN_NAME,ATC.COLUMN_ID AS COLUMN_ID,')
	INSERT INTO #hquery(cmd) VALUES('ATC.DATA_TYPE AS DATA_TYPE,')
	INSERT INTO #hquery(cmd) VALUES('DECODE(ATC.CHAR_COL_DECL_LENGTH,NULL,ATC.DATA_LENGTH,ATC.CHAR_COL_DECL_LENGTH) AS DATA_LENGTH,')
	INSERT INTO #hquery(cmd) VALUES('ATC.DATA_PRECISION AS PREC,')
	INSERT INTO #hquery(cmd) VALUES('ATC.DATA_SCALE AS DATA_SCALE,DECODE(ATC.NULLABLE,''Y'',1,0) AS NULLABLE ')
	INSERT INTO #hquery(cmd) VALUES('FROM ALL_TAB_COLUMNS ATC WHERE ATC.OWNER = ' + QUOTENAME(@owner, ''''))
	INSERT INTO #hquery(cmd) VALUES('AND ATC.TABLE_NAME=' + QUOTENAME(@tablename, ''''))

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

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

	-- populate a temp table with a list of primary key columns from the Oracle publisher
    TRUNCATE TABLE #hquery
	SELECT @InsColumnList    = 'name'
	SELECT @SelectColumnList = 'COLUMN_NAME'

    INSERT INTO #hquery(cmd) VALUES('SELECT ACC.COLUMN_NAME AS COLUMN_NAME ')
	INSERT INTO #hquery(cmd) VALUES('FROM ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC ')
	INSERT INTO #hquery(cmd) VALUES('WHERE AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.OWNER = ACC.OWNER AND AC.TABLE_NAME = ACC.TABLE_NAME ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.CONSTRAINT_TYPE = ''P'' ')
    INSERT INTO #hquery(cmd) VALUES('AND AC.OWNER = ' + QUOTENAME(@owner, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME=' + QUOTENAME(@tablename, ''''))	

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

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

    -- Mark pk columns
    UPDATE  #publishercolumns
    SET     ispkcol = CONVERT(bit, 1)
    FROM    #publishercolumns pc,
            #publisherpkcolumns pkc
    WHERE   pc.name = pkc.name

	-- Join column info with data type mappings
	SELECT	pc.name as [column_name],
			pc.column_ordinal as [column_ordinal],
			sys.fn_ORAsourceinfo(pc.type, pc.length, pc.prec, pc.scale, pc.isnullable) as [source_info],
			hdm.destination_type as [dest_datatype],
			case hdm.destination_length
				when -1 then pc.length
				else hdm.destination_length
			end as [dest_length],
			case hdm.destination_precision
				when -1 then
					case
						when pc.scale > pc.prec then pc.scale
						else pc.prec
					end
				else hdm.destination_precision
			end as [dest_precision],
			case hdm.destination_scale
				when -1 then pc.scale
				else hdm.destination_scale
			end as [dest_scale],
			case
                when hdm.destination_nullable = 0 then convert(bit, 0)
                else convert(bit, pc.isnullable)
            end as [dest_nullable],
			convert(bit, hdm.is_default) as [is_default],
			convert(bit, pc.ispkcol) as [is_pkcol],
			CONVERT(bit, 1) as [is_valid],
			hdm.dataloss as [dataloss],
			hdm.mapping_id as [mapping_id]
	FROM	#publishercolumns pc,
			sys.fn_helpdatatypemap
			(
				@publisher_dbms,
				@publisher_version,
				'%',
				'MSSQLSERVER',
				NULL,
				'%',
				0
			) hdm
	WHERE	hdm.source_type collate database_default = sys.fn_ORAgetbasetype(pc.type)
	  AND	(
				(hdm.source_length_min is null and hdm.source_length_max is null and pc.length is null) or
				(hdm.source_length_min is not null and
				 hdm.source_length_max is not null and
				 pc.length between hdm.source_length_min and hdm.source_length_max
				)
			)
	  AND	(
				(hdm.source_precision_min is null and hdm.source_precision_max is null and pc.prec is null) or
				(hdm.source_precision_min is not null and
				 hdm.source_precision_max is not null and
				 pc.prec between hdm.source_precision_min and hdm.source_precision_max
				)
			)
	  AND	(
				(hdm.source_scale_min is null and hdm.source_scale_max is null and pc.scale is null) or
				(hdm.source_scale_min is not null and
				 hdm.source_scale_max is not null and
				 pc.scale between hdm.source_scale_min and hdm.source_scale_max
				)
			)
	ORDER BY [column_ordinal], [is_default] DESC, [dest_datatype]

	RETURN (0)
END

 
Last revision 2008RTM
See also

  sp_MSdrop_replcom (Procedure)
sp_MSrepl_enumtablecolumninfo (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