Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdropguidcolumn

  No additional text.


Syntax
create procedure sys.sp_MSdropguidcolumn
    @tablename nvarchar(517)
as
    declare @quoted_column_name nvarchar(258)
    declare @quoted_index_name nvarchar(258)
    declare @quoted_constraint_name nvarchar(258)
    declare @column_id int
    declare @constraint_id int
    declare @object_id int

    set @object_id= object_id(@tablename)

    select @quoted_column_name= quotename(name),
           @column_id= column_id
       from sys.columns
       where object_id = @object_id and is_rowguidcol = 1

    -- Return if there is no rowguidcol. This might be the case because snapshot/merge
    -- were not run yet, so the column has not been added yet.
    if @quoted_column_name is null return 0

    -- Return error if the article status does not say it is about to be removed.
    if exists (select * from dbo.sysmergearticles where objid=@object_id and status<>7)
    begin
        return 1
    end

    select distinct @quoted_index_name= quotename(i.name)
        from sys.indexes i
             join
             sys.index_columns ic on i.index_id=ic.index_id and i.object_id=ic.object_id
             join
             sys.columns c on ic.object_id=c.object_id and ic.column_id=c.column_id
        where i.object_id=@object_id and
              c.column_id = @column_id and
              ic.key_ordinal > 0

    select @quoted_constraint_name= quotename(name),
           @constraint_id = object_id
        from sys.default_constraints
        where parent_object_id = @object_id and parent_column_id = @column_id

    if @quoted_column_name is not NULL
    begin
        exec('alter table ' + @tablename + ' alter column ' + @quoted_column_name + ' drop rowguidcol')
        if @@error<>0 return @@error
    end

    if @quoted_index_name is not NULL
    begin
        exec('drop index ' + @quoted_index_name + ' on ' + @tablename)
        if @@error<>0 return @@error
    end

    if @quoted_constraint_name is not NULL
    begin
        exec('alter table ' + @tablename + ' drop constraint ' + @quoted_constraint_name)
        if @@error<>0 return @@error
    end

    if @quoted_column_name is not NULL
    begin
        exec('alter table ' + @tablename + ' drop column ' + @quoted_column_name)
        if @@error<>0 return @@error
    end

    return 0

 
Last revision 2008RTM
See also

  sp_MSarticlecleanup (Procedure)
sp_MSdeletelightweightsubscription (Procedure)
sp_MSdrop_rlcore (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