Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_IHscripttable

  No additional text.


Syntax


-- Name:
--          sp_IHscripttable

-- Description:
--          Script table object

-- Inputs:
--			@article_id	== article id

-- Security:
--          Internal (used by script generation)

-- Returns:
--          Success or failure
--			Temp table (#proctext) of commands

-- Owner:
--          


CREATE PROCEDURE sys.sp_IHscripttable
(
	@article_id	int,
    @collation  sysname
)
AS
BEGIN
	declare @retcode		int
	declare @cmd			nvarchar(4000)
	declare @dest_owner		nvarchar(255)
	declare @dest_tabname	sysname
	declare @col_name		sysname
	declare @col_type		tinyint
	declare	@col_length		bigint
	declare @col_prec		int
	declare	@col_scale		int
	declare @col_nullable	nvarchar(10)
	declare @typestring		nvarchar(255)
	
	-- Get sysarticles info
	select	@dest_owner		= dest_owner,
			@dest_tabname	= dest_table
	from	sysarticles
	where	artid = @article_id
	
	if @dest_owner is not null
	begin
	    select @dest_owner = QUOTENAME( @dest_owner ) + N'.'
	end
	else
	begin
	    select @dest_owner = N''
	end
	
	-- Script out CREATE TABLE statement
	select @cmd = N'CREATE TABLE ' + @dest_owner + QUOTENAME(@dest_tabname) + N'('
	
	insert into #proctext(procedure_text)
	values(@cmd)
	
	-- Script column list
	DECLARE hArtCol CURSOR LOCAL FAST_FORWARD FOR
	SELECT	ihc.name,
			ihc.mapped_type,
			ihc.mapped_length,
			ihc.mapped_prec,
			ihc.mapped_scale,
			case ihpc.isnullable
				when 0 then N'NOT NULL'
				else N'NULL' end
	FROM	IHcolumns ihc WITH ( INDEX (idx_IHcolumns_articleid) ),
		IHpublishercolumns ihpc WITH ( INDEX (idx_IHpublishercolumns_pubcolumnid) )
	WHERE	ihpc.publishercolumn_id = ihc.publishercolumn_id
	  AND	ihc.article_id = @article_id
	ORDER BY ihpc.column_ordinal
	OPTION ( LOOP JOIN, FORCE ORDER )

	OPEN	hArtCol
	FETCH	hArtCol
	INTO 	@col_name,
			@col_type,
			@col_length,
			@col_prec,
			@col_scale,
			@col_nullable
	
	WHILE (@@fetch_status <> -1)
	BEGIN
		-- Construct type string
		SELECT @typestring = QUOTENAME(type_name(@col_type))


        IF type_name(@col_type) IN (N'datetime2',N'time',N'datetimeoffset')
        BEGIN
			SELECT @typestring = @typestring + N' (' + convert(nvarchar(255), @col_scale) + N')'
        END
		ELSE IF @col_length > 0
		BEGIN
			SELECT @typestring = @typestring + N' (' + convert(nvarchar(255), @col_length) + N')'
		END
		ELSE IF @col_length = -1
		BEGIN
			-- Special case - max types
			SELECT @typestring = @typestring + N' (max)'
		END
		ELSE IF @col_prec > 0
		BEGIN
			SELECT @typestring = @typestring + N' (' + convert(nvarchar(255), @col_prec)
			
			IF @col_scale >= 0
			BEGIN
				SELECT @typestring = @typestring + N', ' + convert(nvarchar(255), @col_scale)
			END
			
			SELECT @typestring = @typestring + N')'
		END

	    SELECT @cmd = char(9) + QUOTENAME(@col_name) + N' ' + @typestring + N' ' +
                      CASE
                        WHEN @collation IS NOT NULL AND type_name(@col_type) IN (N'char', N'varchar', N'nchar', N'nvarchar', N'text', N'ntext')
                            THEN N'COLLATE ' + @collation + N' '
                        ELSE N''
                      END + @col_nullable + ','

	    INSERT INTO #proctext(procedure_text)
		VALUES(@cmd)

		FETCH	hArtCol
		INTO 	@col_name,
				@col_type,
				@col_length,
				@col_prec,
				@col_scale,
				@col_nullable
	END
	
	CLOSE hArtCol
	DEALLOCATE hArtCol

	-- Remove trailing ','
	update #proctext
	set		procedure_text = left(procedure_text, len(procedure_text) - 1)
	where 	seq = IDENT_CURRENT('#proctext')
	
	-- End create table
	insert into #proctext(procedure_text) values( N')' )

	RETURN (0)
END

 
Last revision 2008RTM
See also

  sp_IHScriptSchFile (Procedure)
sp_MSdrop_repltran (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