How do I identify the replication generated column(s) in a table

I need to dynamically generate a select command that does not include
replication generated columns. Is there a way to do this? I could use
GetOleDbSchemaTable and filter for column names with "rowguid" but that does
not seem robust to me. Is there a system table or stored procedure that can
help? Dropping a replication subscription and publication removes these
columns so I suspect that the information is available somewhere.
Thanks
Dropping a publication doesn't drop the guid columns added to a merge
publication. As far as I know we can't identify those columns added.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||What about something like this:
declare @.mystring varchar(2000)
set @.mystring='select '
select @.mystring=@.mystring+' '+name+', ' From syscolumns where
id=object_id('customers')
and name <>'rowguid'
select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from customers'
print @.mystring
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kohn" <Kohn@.discussions.microsoft.com> wrote in message
news:915755D4-E2FC-4790-B0E8-6E493DE1BA4F@.microsoft.com...
>I need to dynamically generate a select command that does not include
> replication generated columns. Is there a way to do this? I could use
> GetOleDbSchemaTable and filter for column names with "rowguid" but that
> does
> not seem robust to me. Is there a system table or stored procedure that
> can
> help? Dropping a replication subscription and publication removes these
> columns so I suspect that the information is available somewhere.
> Thanks
>
|||Hi Hilary - unfortunately this doesn't work . We could query for the
rowguid column on the table, but that might already have existed prior to
the replication setup. As far as I can tell there isn't a way of knowing if
the rowguid column is added by the replication setup or by a user
beforehand.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||It will work for the majority of the cases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O$uP0uJNHHA.3424@.TK2MSFTNGP02.phx.gbl...
> Hi Hilary - unfortunately this doesn't work . We could query for the
> rowguid column on the table, but that might already have existed prior to
> the replication setup. As far as I can tell there isn't a way of knowing
> if the rowguid column is added by the replication setup or by a user
> beforehand.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Another thing to take into account is that replication does not require
the column to be called rowguid. It just requires a column with the
ROWGUIDCOL property set. Maybe that is where you should start looking.
Hilary Cotter wrote:
> What about something like this:
> declare @.mystring varchar(2000)
> set @.mystring='select '
> select @.mystring=@.mystring+' '+name+', ' From syscolumns where
> id=object_id('customers')
> and name <>'rowguid'
> select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from customers'
> print @.mystring
>
|||I had a quick look and it seems you have to look for colomns where
syscolumns.colstat = 2. You can also only have one column per table with
the ROWGUIDCOL property set, so you can be pretty sure that is the
column used by replication. So to modify Hilary's query:
declare @.mystring varchar(2000)
set @.mystring='select '
select @.mystring=@.mystring+' '+name+', ' From syscolumns where
id=object_id('customers')
and colstat <> 2
select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from
customers'
print @.mystring
JE wrote:[vbcol=seagreen]
> Another thing to take into account is that replication does not require
> the column to be called rowguid. It just requires a column with the
> ROWGUIDCOL property set. Maybe that is where you should start looking.
>
> Hilary Cotter wrote:
|||The information for identifying the column with the rowguid property solves
the problem. My app retrieves the information with the GetOleDbSchemaTable
function (see below).
By the way, rowguid columns created by the wizard are removed when dropping
the publication. I suspect it uses the preserve_rowguidcol column in the
sysmergearticles tables.
cn.Open()
Dim t As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
New Object() {Nothing, Nothing, TableName, Nothing})
cn.Close()
cn.Dispose()
Dim SelectRows() As DataRow
SelectRows = t.Select("(DATA_TYPE <> 72) AND
(COLUMN_HASDEFAULT=FALSE) AND (COLUMN_HASDEFAULT=False) AND
((ISNULL(COLUMN_DEFAULT,'T')='T') OR
(COLUMN_DEFAULT<>'(newsequentialid())'))")
Dim SelectListStringBuilder As New System.Text.StringBuilder
For Each r As DataRow In SelectRows
SelectListStringBuilder.Append(r.Item("COLUMN_NAME "))
SelectListStringBuilder.Append(",")
Next
SelectListStringBuilder.Length -= 1
Debug.WriteLine(SelectListStringBuilder.ToString)
Thanks for the help