Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSgetviewcolumnlist

  No additional text.


Syntax

create procedure sys.sp_MSgetviewcolumnlist(
@pubid              uniqueidentifier,
@source_objid       int,
@column_list        nvarchar(max) OUTPUT,
@prefix_tablename   sysname = NULL,
@blob_cols_at_the_end  bit = 0,
@explicit_column_list bit = 0
)
AS
/*
** if it is not vertically partitioned, then get all columns
** else get the column list as given in columns of dbo.sysmergearticles
*/
declare @bitset         int
declare @columns        varbinary(125)
declare @setcolcnt      int
declare @colcnt         int
declare @colname        nvarchar(140)
declare @colid          int
declare @table_has_blob bit
declare @loop_counter   int
declare @loop_counter_max int
declare @column_is_blob int

select @columns = columns from dbo.sysmergearticles where objid = @source_objid and pubid=@pubid
select @table_has_blob = 0
select @loop_counter = 1
select @column_list = ''
select @setcolcnt   = 0
select @colcnt = count(*) from sys.columns where object_id = @source_objid

-- loop through the columns twice if blobs need to put at the end.
if @blob_cols_at_the_end <> 1
    select @loop_counter_max = 1
else
    select @loop_counter_max = 2


while @loop_counter <= @loop_counter_max
begin
    declare collist CURSOR LOCAL FAST_FORWARD FOR
        select name, column_id, sys.fn_IsTypeBlob(system_type_id, max_length) from sys.columns where object_id = @source_objid and is_computed <> 1 and type_name(user_type_id) <> 'timestamp' order by column_id ASC
    FOR READ ONLY
    open collist
    fetch collist into @colname, @colid, @column_is_blob
    WHILE (@@fetch_status <> -1)
    BEGIN
        set @bitset = 1
        if @columns is NOT NULL
            exec @bitset = sys.sp_MStestbit @columns, @colid
        if @bitset<>0
        begin
            -- set table_has_blob is alteast one column is blob
            if @column_is_blob = 1
            begin
                -- if this proc is not called with blob_cols_at_the_end then
                -- ignore the fact that this column is a blob column.
                if @blob_cols_at_the_end <> 1
                    select @column_is_blob = 0
                else
                    select @table_has_blob = 1
            end

            select @colname = QUOTENAME(@colname) --previously we use rowguidcol to replace 'rowguid'
            if @column_list=''                    --which can cause problems and is not necessary.
            begin
                -- Insert non blob column in the first iteration through the loop
                -- so that they are before any blob columns.
                -- Insert blob columns in the second iteration through the loop
                -- so that they are in the end of the column_list.
                if ((@column_is_blob <> 1 and @loop_counter=1) or (@column_is_blob=1 and @loop_counter=2))
                begin
                    select @setcolcnt = @setcolcnt + 1
                    if @prefix_tablename is not NULL
                        select @column_list = @prefix_tablename + @colname
                    else
                        select @column_list = @colname
                end
            end
            else
               begin
                -- Insert non blob column in the first iteration through the loop
                -- so that they are before any blob columns.
                -- Insert blob columns in the second iteration through the loop
                -- so that they are in the end of the column_list.
                if ((@column_is_blob <> 1 and @loop_counter=1) or (@column_is_blob=1 and @loop_counter=2))
                begin
                    select @setcolcnt = @setcolcnt + 1
                    if @prefix_tablename is not NULL
                        select @column_list = @column_list + ', ' + @prefix_tablename + @colname
                    else
                        select @column_list = @column_list + ', ' + @colname
                end
            end
        end
    fetch next from collist into @colname, @colid, @column_is_blob
    END
    close collist
    deallocate collist

    -- increment loop_counter
    select @loop_counter = @loop_counter + 1
end -- end of while @loop_counter <= 2

if (@explicit_column_list = 0)
    begin
        -- Use * only if we didn't skip any columns and
        -- we didn't rearrange any blob columns.
        if @setcolcnt = @colcnt and @table_has_blob <> 1
        begin
            if @prefix_tablename is not NULL
                select @column_list = @prefix_tablename + '*'
            else
                select @column_list = ' * '
        end
    end
return 0

 
Last revision 2008RTM
See also

  sp_MScreate_article_logical_record_views (Procedure)
sp_MSdrop_rlrecon (Procedure)
sp_MSenumchangesdirect (Procedure)
sp_MSenumpartialchangesdirect (Procedure)
sp_MSvalidatearticle (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