/*------------------------------------------------------------------------------

SP1_SERV.SQL

THIS SCRIPT TAKES THE SERVER-SIDE SYSTEM-PROCS FROM 8.0 to SP1.

Changes in this file are organized as follows (please maintain):
	System Tables (UPGRADE.SQL)
	System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL)
	Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
	Schema Procs (OLEDBSCH.SQL / ANSIVIEW.SQL)
	ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
	SEM SQLDMO System Procs (SQLDMO.SQL)

Changes to these scripts should NOT be placed in this file:
	Starfighter Procs (XPSTAR.SQL / INSTMSDB.SQL / SQLTRACE.SQL / WEB.SQL)
	Doc's Samples (INSTPUBS.SQL / INSTNWND.SQL)
	Replication Procs (REPLSYS.SQL / REPLCOM.SQL / REPLTRAN.SQL / REPLMERG.SQL)
These components will maintain separate upgrade scripts.

Notes:
+ Catalog-updates and sp_MS_upd_sysobj_category are enabled for the entire
	file.  Do not disable or re-enable them.  Please do not change set options.

------------------------------------------------------------------------------*/


--------------------------------------------------------------------------------
-- VERIFY Server is started in single-user-mode (catalog-updates enables), and
--	start marking of system-objects.
--------------------------------------------------------------------------------
execute sp_configure 'allow updates',1
go

reconfigure with override
go

exec sp_MS_upd_sysobj_category 1
go

--------------------------------------------------------------------------------
--	System Tables (UPGRADE.SQL)
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--	System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) English
--------------------------------------------------------------------------------
DELETE sysmessages
    where error in
    (15355, 21074, 21396
    ,8668, 8669
    ,13089, 13090, 13091
    ,7619
    ,1959, 15248
    ,21513
    ,21512
    ,21515, 21516, 21517, 21518, 21519, 21520
    ,14359
    ,8526
)
go

-- 21396 has no corresponding new message.  It has been replaced with an updated 21074

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(15355 ,10 ,0 ,'''sys'' will be a reserved user or role name in next version of SQL Server.', 1033)
insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21074,16,0, 'The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.', 1033)
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (8668,16 ,0 ,'An index cannot be created on the view ''%.*ls'' because the select list of the view contains a non-aggregate expression.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (8669,16 ,0 ,'The indexed view ''%.*ls'' is not updatable.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(13089 ,10 ,0 ,'lock' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(13090 ,10 ,0 ,'thread' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(13091 ,10 ,0 ,'communication buffer' ,1033)
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid) values
        (7619, 16, 0, 'Execution of a full-text operation failed. %ls', 1033)
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(1959, 16 ,0 ,'Cannot create an index on a view or computed column because the compatibility level of this database is less than 80.' ,1033)
insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(15248 ,11 ,0 ,'Either the parameter @objname is ambiguous or the claimed @objtype (%s) is wrong.' ,1033)
go

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21513,18,0,'Foreign key column ''%s'' cannot be excluded from a vertical partition.', 1033)
go

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21512,18,0,'%ls: The %ls parameter is shorter than the minimum required size.', 1033)
GO

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21515,18,0,'Replication custom procedures will not be scripted because the specified publication ''%s'' is a snapshot publication.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21516,10,0,'Transactional replication custom procedures for publication ''%s'' from database ''%s'':', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21517,10,0,'Replication custom procedures will not be scripted for article ''%s'' because the auto-generate custom procedures schema option is not enabled.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21518,0,0,'Replication custom procedures for article ''%s'':', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21519,10,0,'Custom procedures will not be scripted for article update commands based on direct INSERT, UPDATE, or DELETE statements.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21520,10,0,'Custom procedure will not be scripted because ''%s'' is not a recognized article update command syntax.', 1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (14359, 16, 0, 'Active Directory is either not enabled on the network or not supported by the operating system.', 1033)
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (8526, 16, 0, 'Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.', 1033)
go
--------------------------------------------------------------------------------
--	System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) Localized
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--	Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
--------------------------------------------------------------------------------
update spt_values 
	set low = -2147483648 
	where number = 1535 and type = 'C'

update spt_values 
	set low = -2147483648 
	where number = 1549 and type = 'C'

if object_id('sp_helptext','P') IS NOT NULL
	drop procedure sp_helptext
if object_id('sp_droplogin','P') IS NOT NULL
	drop procedure sp_droplogin
if object_id('sp_addsrvrolemember','P') IS NOT NULL
	drop procedure sp_addsrvrolemember
if object_id('sp_dropsrvrolemember','P') IS NOT NULL
	drop procedure sp_dropsrvrolemember
if object_id('sp_grantdbaccess','P') IS NOT NULL
	drop procedure sp_grantdbaccess
if object_id('sp_addalias','P') IS NOT NULL
	drop procedure sp_addalias
if object_id('sp_addrole','P') IS NOT NULL
	drop procedure sp_addrole
if object_id('sp_addapprole','P') IS NOT NULL
	drop procedure sp_addapprole
if object_id('sp_change_users_login','P') IS NOT NULL
    drop procedure sp_change_users_login
if object_id('sp_fulltext_table','P') IS NOT NULL
	drop procedure sp_fulltext_table
if object_id('sp_fulltext_column','P') IS NOT NULL
	drop procedure sp_fulltext_column
go

create procedure sp_droplogin
	@loginame sysname
as

declare @exec_stmt nvarchar(890)

    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare	@sid	varbinary(85)

	/*Create temp tables before any DML to ensure dynamic*/
    -- CREATE TEMPORARY TABLES FOR LATER USE --
   	create table #db_list (dbname sysname collate database_default not null, user_name sysname collate database_default not null)
	create table #retval (job_count int not null)

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
	begin
	   dbcc auditevent (104, 2, 0, @loginame, NULL, NULL, NULL)
	   raiserror(15247,-1,-1)
	   return (1)
	end
	ELSE
	begin
	   dbcc auditevent (104, 2, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_droplogin')
		return (1)
	end

    -- VALIDATE LOGIN NAME (SQL LOGIN) --
	select @sid = sid from master.dbo.syslogins
        where loginname = @loginame and isntname = 0
	if (@sid is null)
	begin
		raiserror(15007,10,-1,@loginame)
		return(1)
	end
    -- CANNOT CHANGE SA ROLES --
	else if @sid = 0x1	-- 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

	-- CHECK IF @sid IS CURRENTLY LOGGED IN (ignore cached remote connections) --
	if exists(select * from master.dbo.sysprocesses where sid = @sid and status != 'dormant')
	begin
		raiserror(15434, -1, -1, @loginame)
		return(1)
	end

    -- CHECK IF ANY DATABASES ARE OWNED BY LOGIN --
	if exists(select * from master.dbo.sysdatabases where sid = @sid)
	begin
		raiserror(15174, -1, -1, @loginame)
		select 'Databases owned by login:' = name
                from master.dbo.sysdatabases where sid = @sid
		return(1)
	end

	-- COLLECT ALL INSTANCES OF USE OF THIS LOGIN IN SYSUSERS --
	declare @dbname		sysname
	declare ms_crs_dbname cursor local keyset for select name from master.dbo.sysdatabases
	open ms_crs_dbname
	fetch ms_crs_dbname into @dbname
	while @@fetch_status >= 0
	begin
		if (has_dbaccess(@dbname) = 1)
		begin
			select @exec_stmt = 'use ' + quotename( @dbname , '[') + '
				   insert into #db_list (dbname, user_name)
				select N'+ quotename( @dbname , '''')+', name from sysusers
				where sid = suser_sid(N' + quotename( @loginame , '''') + ') '
			exec (@exec_stmt)
		end
		else
			raiserror(15622,-1,-1, @dbname)

		fetch ms_crs_dbname into @dbname
	end
	deallocate ms_crs_dbname

    -- ERROR IF LOGIN USED AS USER IN ANY DATABASE --
	if (select count(*) from #db_list) <> 0
	begin
		raiserror(15175,-1,-1,@loginame)
		select
			'Database name:' = dbname,
			'User name:' = user_name,
			'Mapping type:' = 'user'
		from #db_list
		order by dbname
		return (1)
	end

    -- VERIFY NO JOBS IN MSDB OWNED BY THIS LOGIN --
	if db_id('msdb') is not null
        and object_id('msdb.dbo.sp_check_for_owned_jobs') is not null
	begin
        exec msdb.dbo.sp_check_for_owned_jobs @loginame, '#retval'
	    if exists (select job_count from #retval where job_count > 0)
	    begin
		    declare @job_count int
		    select @job_count = job_count from #retval
		    raiserror(14248, -1, -1, @job_count)
		    return (1)
	    end
	end

    -- DELETE THIS LOGIN (ALSO DELETES REMOTE LOGINS MAPPED TO IT) --
	delete from master.dbo.sysxlogins where sid = @sid

    -- FINALIZATION: SUCCESS/FAILURE MESSAGE
	if @@rowcount > 0
	begin
		-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
		exec('use master grant all to null')

		raiserror(15479,-1,-1)
		return (0)
	end
	else
	begin
		raiserror(15007,10,-1,@loginame)
		return (1)
	end     -- sp_droplogin
go

create procedure sp_addsrvrolemember
    @loginame sysname,			-- login name
    @rolename sysname = NULL	-- server role name
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @rolebit    smallint,
            @ismem      int,
            @sid        varbinary(85)

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addsrvrolemember')
		return (1)
	end

    -- VALIDATE SERVER ROLE NAME, CHECKING PERMISSIONS --
    select @ismem = is_srvrolemember(@rolename)
    if @ismem is null
    begin
		dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL)
        raiserror(15402, -1, -1, @rolename)
        return (1)
    end
    if @ismem = 0
	begin
		dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end

	-- AUDIT A SUCCESSFUL SECURITY CHECK --
	dbcc auditevent (108, 1, 1, @loginame, NULL, @rolename, NULL)

    -- OBTAIN THE BIT FOR THIS ROLE --
    select @rolebit = CASE @rolename
            WHEN 'sysadmin'         THEN 16
            WHEN 'securityadmin'    THEN 32
            WHEN 'serveradmin'      THEN 64
            WHEN 'setupadmin'       THEN 128
            WHEN 'processadmin'     THEN 256
            WHEN 'diskadmin'        THEN 512
            WHEN 'dbcreator'        THEN 1024
			WHEN 'bulkadmin'		THEN 4096
            ELSE NULL END

	select @sid = sid from master.dbo.syslogins where loginname = @loginame
    -- ADD ROW FOR NT LOGIN IF NEEDED --
    if @sid is null
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
	    begin
		    raiserror(15007,-1,-1,@loginame)
		    return (1)
	    end
    end
    -- CANNOT CHANGE SA ROLES --
	else if @sid = 0x1	-- 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

    -- UPDATE ROLE MEMBERSHIP --
    update master.dbo.sysxlogins set xstatus = xstatus | @rolebit, xdate2 = getdate()
	    where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

	raiserror(15488,-1,-1,@loginame,@rolename)

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	return (@@error) -- sp_addsrvrolemember
go

create procedure sp_dropsrvrolemember
    @loginame sysname,			-- login name
    @rolename sysname = NULL	-- server role name
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @rolebit    smallint,
            @ismem      int,
			@sid		varbinary(85)

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_dropsrvrolemember')
		return (1)
	end

    -- VALIDATE SERVER ROLE NAME, CHECKING PERMISSIONS --
    select @ismem = is_srvrolemember(@rolename)
    if @ismem is null
    begin
		dbcc auditevent (108, 2, 0, @loginame, NULL, @rolename, NULL)
        raiserror(15402, -1, -1, @rolename)
        return (1)
    end
    if @ismem = 0
	begin
		dbcc auditevent (108, 2, 0, @loginame, NULL, @rolename, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end

	-- AUDIT THE SUCCESSFUL SECURITY CHECK --
	dbcc auditevent (108, 2, 1, @loginame, NULL, @rolename, NULL)

    -- OBTAIN THE BIT FOR THIS ROLE --
    select @rolebit = CASE @rolename
            WHEN 'sysadmin'         THEN 16
            WHEN 'securityadmin'    THEN 32
            WHEN 'serveradmin'      THEN 64
            WHEN 'setupadmin'       THEN 128
            WHEN 'processadmin'     THEN 256
            WHEN 'diskadmin'        THEN 512
            WHEN 'dbcreator'        THEN 1024
			WHEN 'bulkadmin'		THEN 4096
            ELSE NULL END

	select @sid = sid from master.dbo.syslogins where loginname = @loginame
	-- ERROR IF USER DOESNT EXIST --
	if @sid is null
    begin
	    raiserror(15007,-1,-1,@loginame)
	    return (1)
    end
    -- CANNOT CHANGE SA ROLES --
	else if @sid = 0x1	-- 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

    -- UPDATE ROLE MEMBERSHIP --
    update master.dbo.sysxlogins set xstatus = xstatus & ~@rolebit, xdate2 = getdate()
	    where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

	raiserror(15489,-1,-1,@loginame,@rolename)

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	return (@@error) -- sp_dropsrvrolemember
go

create procedure sp_grantdbaccess
	@loginame       sysname,
	@name_in_db     sysname = NULL OUT
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @uid        smallint,
            @sid        varbinary(85),
            @status     smallint

    if @name_in_db is null
        select @name_in_db = @loginame

    -- CHECK PERMISSIONS --
    if (not is_member('db_accessadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
        dbcc auditevent (109, 3, 0, @loginame, @name_in_db, NULL, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	else
	begin
        dbcc auditevent (109, 3, 1, @loginame, @name_in_db, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_grantdbaccess')
		return (1)
	end

    -- VALIDATE NAME-IN-DB --
    if @name_in_db <> @loginame
    begin
		exec @ret = sp_validname @name_in_db
		if @ret <> 0
			return(1)
        if (charindex('\', @name_in_db) > 0)
        begin
            raiserror(15006,-1,-1,@name_in_db)
            return (1)
        end
    end

    -- CHECK FOR SPECIAL USER GUEST --
    if @name_in_db = 'guest'
    begin
        -- ERROR IF NOT USER, OR ALREADY ADDED --
        if @loginame <> 'guest'
        begin
		    raiserror(15062,-1,-1)
		    return(1)
        end
        if exists (select * from sysusers where hasdbaccess = 1 and name = 'guest')
        begin
            raiserror(15023,-1,-1,'guest')
            return (1)
        end

        -- ENABLE USER GUEST --
        update sysusers set status = (status & ~1) | 2, updatedate = getdate()
                    where name = 'guest'
        return (0)
    end

    -- VALIDATE LOGIN NAME (OBTAIN SID) --
    select @status = case when (charindex('\', @loginame) <> 0) then 4 else 0 end
    if @status = 0
        select @sid = sid from master.dbo.syslogins         -- sql user
            where isntname = 0 and loginname = @loginame
    if @sid is null
    begin
        -- NT GROUPS REQUIRE DOMAIN NAME --
        if @status = 4
            select @sid = get_sid('\G'+@loginame, NULL)     -- nt group
        if @sid is null
        begin
            select @sid = get_sid('\U'+@loginame, NULL)     -- nt user
            if @sid is not null
                select @status = 12
        end
    end
    -- PREVENT USE OF CERTAIN LOGINS --
	else if @sid = 0x1	-- 'sa'
	begin
		raiserror(15405, -1, -1, @loginame)
		return (1)
	end

    if @sid is null
    begin
        if @status = 0
            raiserror(15007,-1,-1,@loginame)
        else
            raiserror(15401,-1,-1,@loginame)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY IN DATABASE --
    if exists (select sid from sysusers where sid = @sid)
    begin
        -- ERROR IF LOGIN IS ALREADY ALIASED --
        if exists (select sid from sysusers where sid = @sid and isaliased = 1)
        begin
		    raiserror(15022,-1,-1)
		    return (1)
        end

        -- ERROR IF ALREADY EXISTS UNDER DIFFERENT NAME --
        if (not user_sid(user_id(@name_in_db)) = @sid)
        begin
		    raiserror(15063,-1,-1)
		    return (1)
        end

        -- ERROR IF LOGIN ALREADY HAS ACCESS --
        if exists (select sid from sysusers where sid = @sid and hasdbaccess = 1)
        begin
            if @status = 4
    		    raiserror(15024,-1,-1,@name_in_db)
            else
		        raiserror(15023,-1,-1,@name_in_db)
		    return (1)
        end

        -- GIVE DATABASE ACCESS TO THIS LOGIN --
        update sysusers set status = (status & ~1) | 2, updatedate = getdate()
                    where sid = @sid
        return @@error
	end

	if @name_in_db = 'sys'
		raiserror(15355,-1,-1)

    if user_id(@name_in_db) is not null OR
		@name_in_db IN ('system_function_schema','INFORMATION_SCHEMA')
    begin
        -- SYSUSERS NAME ALREADY EXISTS --
        if @status = 4
    		raiserror(15024,-1,-1,@name_in_db)
        else
		    raiserror(15023,-1,-1,@name_in_db)
        return (1)
    end

    -- OBTAIN NEW UID (RESERVE 1-4) --
    if user_name(5) IS NULL
        select @uid = 5
    else
		select @uid = min(uid)+1 from sysusers
            where uid >= 5 and uid < (16384 - 1)    -- stay in users range
                and user_name(uid+1) is null        -- uid not in use
    if @uid is null
	begin
		raiserror(15065,-1,-1)
		return (1)
	end

    -- INSERT SYSUSERS ROW --
    insert into sysusers select
        @uid, @status | 2, @name_in_db, @sid, 0x00, getdate(), getdate(), 0, NULL

    -- INVALIDATE CACHED PERMISSIONS --
    grant all to null

    -- PRINT SUCCESS --
    raiserror(15341,-1,-1, @loginame)

    -- RETURN SUCCESS STATUS --
    return @@error -- sp_grantdbaccess
go

create procedure sp_addalias
    @loginame       sysname,    -- name of the pretender
    @name_in_db     sysname     -- user to whom to alias the login
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @sid        varbinary(85),
            @targuid    smallint,
            @newuid     smallint,
            @status     smallint,
            @dbname     sysname

    -- CHECK PERMISSIONS --
    if (not is_member('db_accessadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addalias')
		return (1)
	end

    -- VALIDATE LOGIN NAME (OBTAIN SID) --
    select @status = CASE WHEN charindex('\', @loginame) > 0 THEN 12 ELSE 0 END
    if @status = 0
        select @sid = suser_sid(@loginame)          -- sql user
    -- retry sql user as nt with dflt domain
    if @sid is null
    begin
        select @sid = get_sid('\U'+@loginame, NULL) -- nt user
        if @sid is null
        begin
            if @status = 0
                raiserror(15007,-1,-1,@loginame)
            else
                raiserror(15401,-1,-1,@loginame)
            return (1)
        end
        select @status = 12
    end
    -- PREVENT USE OF CERTAIN LOGINS --
	else if @sid = 0x1
	begin
		raiserror(15405, -1, -1, @loginame)
		return (1)
	end

    -- VALIDATE NAME-IN-DB (OBTAIN TARGET UID) --
    select @targuid = uid from sysusers where name = @name_in_db
                        and (issqluser = 1 or isntuser = 1)
						and uid NOT IN (3,4)	-- INFORMATION_SCHEMA, system_function_schema
    if @targuid is null
	begin
		raiserror(15008,-1,-1,@name_in_db)
		return (1)
	end

    -- ERROR IF LOGIN ALREADY IN DATABASE --
    if exists (select sid from sysusers where sid = @sid)
    begin

        -- ERROR IF ALREADY ALIASED --
        if exists (select sid from sysusers where sid = @sid and isaliased = 1)
	    begin
		    raiserror(15022,-1,-1)
		    return (1)
	    end

        -- ERROR: LOGIN ALREADY A USER --
        select @name_in_db = name, @dbname = db_name() from sysusers where sid = @sid
        raiserror(15278,-1,-1,@loginame,@name_in_db,@dbname)
        return (1)
    end

    -- ALTER NAME TO AVOID CONFLICTS IN NAME SPACE --
    select @loginame = '\' + @loginame
    if user_id(@loginame) is not null
    begin
	    raiserror(15023,-1,-1,@loginame)
        return (1)
    end

    -- OBTAIN NEW UID (RESERVE 1-4) --
    if user_name(5) IS NULL
        select @newuid = 5
    else
		select @newuid = min(uid)+1 from sysusers
            where uid >= 5 and uid < (16384 - 1)    -- stay in users range
                and user_name(uid+1) is null        -- uid not in use
    if @newuid is null
	begin
		raiserror(15065,-1,-1)
		return (1)
	end

    -- INSERT SYSUSERS ROW --
    insert into sysusers select
        @newuid, @status | 16, @loginame, @sid, 0x00,
                getdate(), getdate(), @targuid, NULL

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15340,-1,-1)
    return (0) -- sp_addalias
go

create procedure sp_addrole
    @rolename   sysname,        -- name of new role
    @ownername  sysname = 'dbo' -- name of owner of new role
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @uid        smallint,
            @owner      smallint

    -- CHECK PERMISSIONS --
    if (not is_member('db_securityadmin') = 1) and
       (not is_member('db_owner') = 1)
    begin
		dbcc auditevent (111, 1, 0, NULL, NULL, @rolename, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	else
	begin
		dbcc auditevent (111, 1, 1, NULL, NULL, @rolename, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addrole')
		return (1)
	end

	    -- RESOLVE OWNER NAME --
    select @owner = uid from sysusers where name = @ownername
                    and isaliased = 0 AND uid NOT IN (0,3,4) --public/INFO_SCHEMA/etc can't own role
    if @owner is null
    begin
		raiserror(15008,-1,-1,@ownername)
		return (1)
    end

    -- VALIDATE ROLE NAME --
	execute @ret = sp_validname @rolename
	if @ret <> 0
		return (1)
	if (charindex('\', @rolename) > 0)
    begin
        raiserror(15006,-1,-1,@rolename)
        return (1)
    end

	if @rolename = 'sys'
		raiserror(15355,-1,-1)

    -- ERROR IF SYSUSERS NAME ALREADY EXISTS --
    if user_id(@rolename) is not null OR
		@rolename IN ('system_function_schema','INFORMATION_SCHEMA')
    begin
        if exists (select name from sysusers where issqlrole = 1 and name = @rolename)
    		raiserror(15363,-1,-1,@rolename)
        else
		    raiserror(15023,-1,-1,@rolename)

        return (1)
    end

    -- OBTAIN NEW ROLE UID (RESERVE 16384-16399) --
    if user_name(16400) IS NULL
        select @uid = 16400
    else
		select @uid = min(uid)+1 from sysusers
            where uid >= 16400 and uid < (32767 - 1)    -- stay in role range
                and user_name(uid+1) is null            -- uid not in use
    if @uid is null
	begin
		raiserror(15065,-1,-1)
		return (1)
	end

    -- INSERT THE ROW INTO SYSUSERS --
    insert into sysusers values
        (@uid, 0, @rolename, NULL, 0x00, getdate(), getdate(), @owner, NULL)

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15424,-1,-1)
    return (0) -- sp_addrole
go

create procedure sp_addapprole
    @rolename   sysname,        -- name of new app role
    @password   sysname         -- password for app role
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @uid        smallint

	-- CHECK FOR NULL PASSWORD
	if (@password is null)
	begin
		raiserror(15034,-1,-1)
		return (1)
	end

    -- CHECK PERMISSIONS --
    if (not is_member('db_securityadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addapprole')
		return (1)
	end

    -- VALIDATE APPROLE NAME --
	execute @ret = sp_validname @rolename
	if @ret <> 0
		return (1)
	if (charindex('\', @rolename) > 0)
    begin
        raiserror(15006,-1,-1,@rolename)
        return (1)
    end

	if @rolename = 'sys'
		raiserror(15355,-1,-1)

    -- ERROR IF SYSUSERS NAME ALREADY EXISTS --
    if user_id(@rolename) is not null OR
		@rolename IN ('system_function_schema','INFORMATION_SCHEMA')
    begin
        raiserror(15363,-1,-1,@rolename)
        return (1)
    end

    -- OBTAIN NEW APPROLE UID (RESERVE 1-4) --
    if user_name(5) IS NULL
        select @uid = 5
    else
		select @uid = min(uid)+1 from sysusers
            where uid >= 5 and uid < (16384 - 1)    -- stay in users range
                and user_name(uid+1) is null        -- uid not in use
    if @uid is null
	begin
		raiserror(15065,-1,-1)
		return (1)
	end

    -- INSERT THE ROW INTO SYSUSERS --
    insert into sysusers values
        (@uid, 32, @rolename, NULL, 0x00, getdate(),
                    getdate(), 1, convert(varbinary(256), pwdencrypt(@password)))

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15425,-1,-1)
    return (0) -- sp_addapprole
go

CREATE PROCEDURE sp_change_users_login
    @Action               varchar(10)       -- REPORT / UPDATE_ONE / AUTO_FIX
   ,@UserNamePattern      sysname  = Null
   ,@LoginName            sysname  = Null
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @exec_stmt nvarchar(430)

	declare @ret            int,
            @FixMode        char(5),
            @cfixesupdate   int,        -- count of fixes by update
            @cfixesaddlogin int,        -- count of fixes by sp_addlogin
            @dbname         sysname,
            @loginsid       varbinary(85),
            @110name        sysname

    -- SET INITIAL VALUES --
    select  @dbname         = db_name(),
            @cfixesupdate   = 0,
            @cfixesaddlogin = 0

    -- ERROR IF IN USER TRANSACTION --
    if @@trancount > 0
    begin
        raiserror(15289,-1,-1)
        return (1)
    end

    -- INVALIDATE USE OF SPECIAL LOGIN/USER NAMES --
    if suser_sid(@LoginName) = 0x1	-- 'sa'
    begin
        raiserror(15287,-1,-1,@LoginName)
        return (1)
    end
    if user_id(@UserNamePattern) in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, system_function_schema
    begin
        raiserror(15287,-1,-1,@UserNamePattern)
        return (1)
    end

    -- HANDLE REPORT --
    if upper(@Action) = 'REPORT'
    begin

        -- VALIDATE PARAMS --
        if @UserNamePattern IS NOT Null or @LoginName IS NOT Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- GENERATE REPORT --
        select UserName = name, UserSID = sid from sysusers
            where issqluser = 1 and (sid is not null and sid <> 0x0)
                    and suser_sname(sid) is null
            order by name
        return (0)
    end

    -- HANDLE UPDATE_ONE --
    if upper(@Action) = 'UPDATE_ONE'
    begin

        -- CHECK PERMISSIONS --
        if not is_member('db_owner') = 1
        begin
            raiserror(15247,-1,-1)
            return (1)
        end

        -- ERROR IF PARAMS NULL --
        if @UserNamePattern IS Null or @LoginName IS Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- VALIDATE PARAMS --
        -- Can ONLY remap SQL Users to SQL Logins!  Should be no need
        --  for re-mapping NT logins, and if you try, you'll mess up
        --  the user status bits! 
        if not exists (select name from sysusers where
                name = @UserNamePattern             -- match user name
            and issqluser = 1)                      -- must be sql user
        begin
            raiserror(15291,-1,-1,'User',@UserNamePattern)
            return (1)
        end
        select @loginsid = sid from master.dbo.syslogins where
                loginname = @LoginName              -- match login name
            and isntname = 0                        -- cannot use nt logins
        if @loginsid is null
        begin
            raiserror(15291,-1,-1,'Login',@LoginName)
            return (1)
        end

        -- ERROR IF SID ALREADY IN USE IN DATABASE --
        if exists (select sid from sysusers where sid = @loginsid
                    and name <> @UserNamePattern)
        begin
		    raiserror(15063,-1,-1)
		    return (1)
        end

        -- CHANGE THE USERS LOGIN (SID) --
        update sysusers set sid = @loginsid, updatedate = getdate()
                where name = @UserNamePattern and issqluser = 1
                and sid <> @loginsid

        -- FINALIZATION: REPORT (ONLY IF NOT SUCCESSFUL) AND EXIT --
        if @@error <> 0 or @@rowcount <> 1
            raiserror(15295,-1,-1, 0)
        return (0)
    end

    -- ERROR IF NOT AUTO_FIX --
    if upper(@Action) <> 'AUTO_FIX'
    begin
        raiserror(15286,-1,-1,@Action)
        return (1)
    end

    -- HANDLE AUTO_FIX --
    -- CHECK PERMISSIONS --
    if not is_srvrolemember('sysadmin') = 1
    begin
        raiserror(15247,-1,-1)
        return (1)
    end

    -- VALIDATE PARAMS --
    if @UserNamePattern IS Null or @LoginName IS NOT Null
    begin
        raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
        return (1)
    end

    -- LOOP THRU ORPHANED USERS --
	select @exec_stmt = 'DECLARE ms_crs_110_Users cursor global for
            select name from sysusers
            where name = N' + quotename( @UserNamePattern , '''')+ '
                and issqluser = 1 and suser_sname(sid) is null'
    EXECUTE (@exec_stmt)
    OPEN ms_crs_110_Users

    WHILE (110=110)
    begin
        FETCH next from ms_crs_110_Users into @110name
        if (@@fetch_status <> 0)
        begin
            DEALLOCATE ms_crs_110_Users
            BREAK
        end

        -- IS NAME ALREADY IN USE? --
        -- if suser_sid(@110name) is null
		if not exists(select * from master.dbo.syslogins where loginname = @110name)
        begin

            -- ADD LOGIN --
            execute @ret = sp_addlogin @110name, Null, @dbname
            if @ret <> 0 or suser_sid(@110name) is null
            begin
                raiserror(15497,16,1,@110name)
                deallocate ms_crs_110_Users
                return (1)
            end
            select @FixMode = '1AddL'
            raiserror(15293,-1,-1,@110name)
        end
        ELSE
        begin
            Select @FixMode = '2UpdU'
            Raiserror(15292,-1,-1,@110name)
        end

        -- REPORT ERROR & CONTINUE IF DUPLICATE SID IN DB --
        select @loginsid = suser_sid(@110name)
        if user_sid(@loginsid) is not null
        begin
            raiserror(15331,-1,-1,@110name)
            CONTINUE
        end

        -- UPDATE SYSUSERS ROW --
        update sysusers set sid = @loginsid, updatedate = getdate() where name = @110name
        if @@error <> 0
        begin
            raiserror(15498,17,127)
            deallocate ms_crs_110_Users
            return (1)
        end


        if @FixMode = '1AddL'
            Select @cfixesaddlogin = @cfixesaddlogin + 1
        else
            Select @cfixesupdate = @cfixesupdate + 1
    end -- loop 110

    -- REPORT AND RETURN SUCCESS --
    raiserror(15295,-1,-1,@cfixesupdate)
    raiserror(15294,-1,-1,@cfixesaddlogin)
    return (0) -- sp_change_users_login
go

create proc sp_fulltext_table
	@tabname	nvarchar(517),
	@action		varchar(50),
	@ftcat		sysname = NULL,		-- create: catalog name
	@keyname	sysname = NULL		-- create: name of unique index
as
	declare @schemamodified int
	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
        raiserror(15601,-1,-1)
        return 1
	end

	-- VALIDATE PARAMS --
	if @action is null
		OR @action not in ('create','drop','activate','deactivate',
			'start_change_tracking', 'stop_change_tracking',
			'start_background_updateindex', 'stop_background_updateindex',
			'update_index', 'start_full', 'start_incremental', 'stop')
		OR (@action not in ('create') and (@ftcat is not null or @keyname is not null))
		OR (@action in ('create') and (@ftcat is null or @keyname is null))
	begin
        raiserror(15600,-1,-1,'sp_fulltext_table')
        return 1
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
    if @@trancount > 0
    begin
        raiserror(15002,-1,-1,'sp_fulltext_table')
        return 1
    end

	-- VALIDATE TABLE NAME --
    --  (1) Must exist in current database
	declare @objid int
	select @objid = object_id(@tabname, 'local')
    if @objid is null
    begin
		declare @curdbname sysname
	    select @curdbname = db_name()
	    raiserror(15009,-1,-1 ,@tabname, @curdbname)
	    return 1
    end
    --  (2) Must be a user table (and not a temp table)
    if ObjectProperty(@objid, 'IsUserTable') = 0 OR substring(parsename(@tabname,1),1,1) = '#'
    begin
	    raiserror(15218,-1,-1 ,@tabname)
	    return 1
    end

    -- CHECK PERMISSION ON TABLE --
    if (is_member('db_owner') = 0) AND (is_member('db_ddladmin') = 0)
        AND (is_member(user_name(ObjectProperty(@objid, 'ownerid'))) = 0)
    begin
        raiserror(15247,-1,-1)
        return 1
    end

	-- CHECK DATABASE MODE (must not be read-only) --
	if DATABASEPROPERTY(db_name(), 'IsReadOnly') = 1
	begin
		raiserror(15635, -1, -1, 'sp_fulltext_table')
		return 1
	end

	-- BEGIN TRAN AND LOCK TABLE --
	begin tran
	dbcc lockobjectschema(@tabname)
	if @@error <> 0
	begin
		goto error_abort_exit
	end

	-- OBTAIN CATALOG NAME FROM SYSOBJECTS & CHECK ACTION --
	declare @ftcatid smallint
	select @ftcatid = ObjectProperty(@objid, 'TableFulltextCatalogId')
	if @ftcatid <> 0 and @action = 'create'
	begin
        raiserror(15605,-1,-1,@tabname)
        goto error_abort_exit
	end
	if @ftcatid = 0 and @action <> 'create'
	begin
        raiserror(15606,-1,-1,@tabname)
        goto error_abort_exit
	end

	if @action = 'create'
	begin
		-- CHECK CATALOG NAME --
		select @ftcatid = null
		select @ftcatid = ftcatid from sysfulltextcatalogs where name = @ftcat
		if @ftcatid is null
		begin
			raiserror(7641,-1,-1,@ftcat)
			goto error_abort_exit
		end

		-- CHECK INDEX NAME (UNIQUE, SINGLE-KEY, 450-byte MAX, NON-NULLABLE) AND SET BIT IF FOUND --
		if IndexProperty(@objid, @keyname, 'IsUnique') = 1 and
		   IndexProperty(@objid, @keyname, 'UserKeyCount') = 1 and
		   IndexProperty(@objid, @keyname, 'IsHypothetical') = 0 and
		   exists (select * from syscolumns where id = @objid and name = Index_col(@tabname, IndexProperty(@objid, @keyname, 'IndexId'), 1)
				and length <= 450 and isnullable = 0)
		begin
			update sysindexes set status = status | 33554432 where id = @objid
				and name = @keyname and indid > 0 and indid < 255
		end
		else
		begin
			raiserror(15607,-1,-1,@keyname)
			goto error_abort_exit
		end

		-- ADD CATALOG NAME TO SYSOBJECTS --
		update sysobjects set ftcatid = @ftcatid where id = @objid

		-- ADD TO CATALOG
		DBCC CALLFULLTEXT ( 5, @ftcatid, @objid )	-- FTAddURL( @ftcatid, db_id(), @objid )
		if @@error <> 0
			goto error_abort_exit

	end

	if @action = 'drop'
	begin
		-- DROP FROM CATALOG (NO ERROR IF ALREADY DROPPED) --
		DBCC CALLFULLTEXT ( 6, @ftcatid, @objid )	-- FTDropURL( @ftcatid, db_id(), @objid )
		if @@error <> 0
			goto error_abort_exit

		-- DELETE SYSDEPENDS ENTRIES FOR IMAGE COLUMNS, IF ANY --
		delete sysdepends where [id] = @objid and
						  depid = @objid and
						  deptype = 1 and
						  number in ( select colid from syscolumns where [id] = @objid and
																		 type = 34 and
																		 (colstat & 16) = 16 )

		-- REMOVE CATALOG NAME AND BITS FROM SYSTEM TABLES --
		update syscolumns set colstat = colstat & ~80, language = 0 where [id] = @objid
		update sysindexes set status = status & ~33554432 where [id] = @objid
		update sysobjects set status = status & ~200, ftcatid = 0 where [id] = @objid

		-- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
		delete sysfulltextnotify where tableid = @objid

	end

	if @action = 'activate'
	begin

		-- MUST HAVE AT LEAST ONE COLUMN MARKED FOR FULLTEXT INDEXING --
		if not exists (select * from syscolumns where id = @objid and (colstat & 16) = 16)
		begin
			raiserror(15609, -1,-1,@tabname)
			goto error_abort_exit
		end

		-- NO ERROR IF INDEXING ALREADY ACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
		begin
			rollback tran
			return 0
		end


		update sysobjects set status = status | 8 where id = @objid

		if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1)
		begin

			-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
			if @@error <> 0
				goto error_abort_exit

			-- DELETE SYSFULLTEXTNOTIFY ENTRIES
			delete sysfulltextnotify where tableid = @objid

			-- START A FULL CRAWL FOR THE TABLE
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
			if @@error <> 0
				goto error_abort_exit

		end


	end

	if @action = 'deactivate'
	begin
		-- NO ERROR IF INDEXING ALREADY DEACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
		begin
			rollback tran
			return 0
		end

		-- IF TABLE IS NOT ENABLED FOR NOTIFICATIONS --
		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
		begin
			-- SET STATE TO INACTIVE, SCHEMA-MODIFIED
			update sysobjects set status = ((status & ~72) | 128) where id = @objid
		end
		else
		begin
			-- SET STATE TO INACTIVE
			update sysobjects set status = (status & ~8) where id = @objid
		end

		-- DELETE SYSFULLTEXTNOTIFY ENTRIES
		delete sysfulltextnotify where tableid = @objid

		-- STOP EXISTING CRAWL (IMPLICIT STOP WITH WARNING)
		DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
		if @@error <> 0
			goto error_abort_exit


	end

	if @action = 'start_change_tracking'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF TABLE IS ALREADY ENABLED FOR NOTIFICATIONS --
		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
		begin
			raiserror(15631,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF DATABASE IS IN SINGLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15637, -1, -1, @tabname)
		    goto error_abort_exit
		end

		-- CHECK TO SEE IF THERE ARE ANY COLUMNS WHICH ARE NOT IN ROW BLOBS --
		if (select count(*) from syscolumns where
			(id = object_id(@tabname)) and ((xtype = 34) or (xtype = 35) or (xtype = 99)) and
			((colstat & 16) != 0) and (length = 16)) > 0
		begin
			raiserror(15639, -1, -1, @tabname)
		end

		-- STOP EXISTING CRAWL
		DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
		if @@error <> 0
			goto error_abort_exit

		-- DELETE SYSFULLTEXTNOTIFY ENTRIES
		delete sysfulltextnotify where tableid = @objid

		select @schemamodified = ObjectProperty(@objid, 'TableIsFulltextSchemaModified')

		-- SET TABLE TO CT ON. SCHEMA MOD. OFF --
		update sysobjects set status = ((status & ~128) | 64) where id = @objid

		-- COMMIT TRAN -- NESCESSARY TO TURN ON CT BEFORE CRAWL IS KICKED OFF --
		commit tran
		if @@error <> 0
			goto error_abort_exit

		if (@schemamodified = 1)
		begin
			-- START A FULL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
			if @@error <> 0
			begin
				-- NEED TO RUN A FULL POPULATION
				raiserror(15644, -1, -1,@tabname, 'start_full')
				return 1
			end
		end
		else
		begin
			-- START AN INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 1 )
			if @@error <> 0
			begin
				-- NEED TO RUN AN INCREMENTAL POPULATION
				raiserror(15644, -1, -1, @tabname, 'start_incremental')
				return 1
			end
		end
		return 0

	end

	if @action = 'stop_change_tracking'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
		begin
		    rollback tran
	    	    return 0
		end

		if(ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
		begin
	        raiserror(7640,-1,-1, @tabname)	
		end

		-- DISABLE FULLTEXT AUTO PROPAGATION (NO ERROR IF ALREADY DISABLED) --
		DBCC CALLFULLTEXT ( 9, @objid )	-- FTDisableNotify( db_id(), @objid )
		if @@error <> 0
			goto error_abort_exit

		-- TURN OFF ACTIVE BITS IN SYSOBJECTS --
		update sysobjects set status = status & ~192 where id = @objid

		if ((select count(*) from sysfulltextnotify where tableid = @objid) != 0)
		begin
	        raiserror(7638,-1,-1, @tabname)
		end

		-- DELETE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
		delete sysfulltextnotify where tableid = @objid


	end

	if @action = 'start_background_updateindex'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end


		-- ERROR IF TABLE IS NOT ENABLED FOR NOTIFICATIONS --
		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
		begin
	        raiserror(15632,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF FULLTEXT SCHEMA OF THE TABLE HAS BEEN MODIFIED (SHOULD NEVER HAPPEN)--
		if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
		begin
	        raiserror(15640,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF TABLE IS ALREADY ENABLED FOR AUTO PROPAGATION --
		if ObjectProperty(@objid, 'TableFulltextBackgroundUpdateIndexOn') = 1
		begin
			raiserror(15633,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ENABLE TABLE FOR FULLTEXT AUTO PROPAGATION --
		DBCC CALLFULLTEXT ( 10, @ftcatid, @objid ) -- FTEnableAutoProp( @ftcatid, db_id(), @objid )
		if @@error <> 0
			goto error_abort_exit

		-- TURN ON FULLTEXT AUTOPROPAGATION BIT IN SYSOBJECTS --
		update sysobjects set status = status | 128 where id = @objid
	end

	if @action = 'stop_background_updateindex'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		if (ObjectProperty(@objid, 'TableFullTextBackgroundUpdateIndexOn') = 0)
		begin
		    rollback tran
		    return 0
		end

		-- DISABLE FULLTEXT AUTO PROPAGATION (NO ERROR IF ALREADY DISABLED) --
		DBCC CALLFULLTEXT ( 9, @objid )	-- FTDisableNotify( db_id(), @objid )
		if @@error <> 0
			goto error_abort_exit

		-- TURN OFF ACTIVE BITS IN SYSOBJECTS --
		update sysobjects set status = status & ~128 where id = @objid
	end

	if @action = 'update_index'
	begin

		-- ERROR IF TABLE IS NOT ENABLED FOR NOTIFICATIONS --
		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
		begin
	        raiserror(15634,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF TABLE IS NOT ACTIVE ANY MORE --
		if (ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0)
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF FULLTEXT SCHEMA OF THE TABLE HAS BEEN MODIFIED -- THIS SHOULD NEVER HAPPEN
		if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
		begin
	        raiserror(15640,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF DATABASE IS IN SIGNLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15637, -1, -1, @tabname)
		    goto error_abort_exit
		end

		DBCC CALLFULLTEXT ( 11, @ftcatid, @objid )	-- FTStartPropagation( db_id(), @ftcatid, @objid )
		if @@error <> 0
			goto error_abort_exit
	end

	if @action = 'start_full'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF DATABASE IS IN SINGLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15637, -1, -1, @tabname)
		    goto error_abort_exit
		end

		-- RAISE WARNING IF POPULATE STATUS OF THE TABLE IS NOT IDLE
		if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
		begin
	        raiserror(7636,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- DELETE SYSFULLTEXTNOTIFY ENTRIES
		delete sysfulltextnotify where tableid = @objid

		-- START A FULL POPULATION FOR THIS TABLE --
		DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
		if @@error <> 0
			goto error_abort_exit

		if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
		and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
			-- SET TABLE SCHEMA-UNMODIFIED
			update sysobjects set status = status & ~128 where id = @objid

	end

	if @action = 'start_incremental'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF DATABASE IS IN SINGLE USER MODE --
		if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
		begin
			raiserror(15637, -1, -1, @tabname)
		    goto error_abort_exit
		end

		-- RAISE WARNING IF POPULATE STATUS OF THE TABLE IS NOT IDLE
		if (ObjectProperty(@objid, 'TableFulltextPopulateStatus') != 0)
		begin
	        raiserror(7636,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- DELETE SYSFULLTEXTNOTIFY ENTRIES
		delete sysfulltextnotify where tableid = @objid

		-- START AN INCREMENTAL POPULATION FOR THIS TABLE --
		if (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 1)
			and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
			begin
			-- FULL CRAWL IF SCHEMA MODIFIED
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
			if @@error <> 0
				goto error_abort_exit

			-- SET TABLE SCHEMA-UNMODIFIED
			update sysobjects set status = status & ~128 where id = @objid
		end
		else
		begin
			-- INCREMENTAL CRAWL
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 1 )
			if @@error <> 0
				goto error_abort_exit
		end
	end

	if @action = 'stop'
	begin
		-- ERROR IF TABLE IS NOT ACTIVATED --
		if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 0
		begin
	        raiserror(15630,-1,-1, @tabname)
		    goto error_abort_exit
		end

		-- ERROR IF POPULATE STATUS OF THE TABLE IS CRAWLING AND CT ON
		if (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1)
		and ((ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 1)
		or (ObjectProperty(@objid, 'TableFulltextPopulateStatus') = 2))
		begin
		    raiserror(15642,-1,-1, @tabname)
			goto error_abort_exit
		end

		-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
		DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
		if @@error <> 0
			goto error_abort_exit
	end

	-- COMMIT TRAN --
	commit tran
	if @@error <> 0
		goto error_abort_exit

	-- SUCCESS --
	return 0

error_abort_exit:
	rollback tran
	return 1	-- sp_fulltext_table
go

create proc sp_fulltext_column
    @tabname        nvarchar(517),      -- table name
    @colname        sysname,            -- column name
    @action         varchar(20),        -- add | drop
    @language       int = null,         -- LCID of data in the column
    @type_colname   sysname = null      -- column name, valid if colname is img

as
	-- FULLTEXT MUST BE ACTIVE IN DATABASE --
	if DatabaseProperty(db_name(), 'IsFulltextEnabled') = 0
	begin
		raiserror(15601,-1,-1)
		return 1
	end

	-- VALIDATE PARAMS --
	if @action is null or @action not in ('add','drop')
	begin
		raiserror(15600,-1,-1,'sp_fulltext_column')
		return 1
	end

	-- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_fulltext_column')
		return 1
	end

	-- VALIDATE TABLE NAME --
	--	(1) Must exist in current database
	declare @objid int
	select @objid = object_id(@tabname, 'local')
	if @objid is null
	begin
		declare @curdbname sysname
		select @curdbname = db_name()
		raiserror(15009,-1,-1 ,@tabname, @curdbname)
		return 1
	end
	--	(2) Must be a user table
	if ObjectProperty(@objid, 'IsUserTable') = 0
	begin
		raiserror(15218,-1,-1 ,@tabname)
		return 1
	end

	-- CHECK PERMISSION ON TABLE --
	if (is_member('db_owner') = 0) AND (is_member('db_ddladmin') = 0)
		AND (is_member(user_name(ObjectProperty(@objid, 'ownerid'))) = 0)
	begin
		raiserror(15247,-1,-1)
		return 1
	end

	-- CHECK DATABASE MODE (must not be read-only) --
	if DATABASEPROPERTY(db_name(), 'IsReadOnly') = 1
	begin
		raiserror(15635, -1, -1, 'sp_fulltext_column')
		return 1
	end

	-- BEGIN TRAN AND LOCK TABLE --
	begin tran
	dbcc lockobjectschema(@tabname)
	if @@error <> 0
	begin
		goto error_abort_exit
	end

	-- CHECK FOR CATALOG IN SYSOBJECTS --
	declare @ftcatid smallint
	select @ftcatid = ObjectProperty(@objid, 'TableFulltextCatalogId')

	if @ftcatid = 0
	begin
		raiserror(15606,-1,-1,@tabname)
		goto error_abort_exit
	end

	-- VALIDATE COLUMN NAME (CANNOT BE COMPUTED) --
	declare @typename sysname
	select @typename = type_name(ColumnProperty(@objid, @colname, 'SystemType'))
	if @typename is null OR ColumnProperty(@objid, @colname, 'IsComputed') = 1
	begin
		raiserror(15104,-1,-1,@tabname,@colname)
		goto error_abort_exit
	end

	-- VALIDATE PARAMETERS
	if (@action <> 'add' or @typename <> N'image') and @type_colname is not null
	begin
		raiserror(15600, -1, -1, 'sp_fulltext_column')
		goto error_abort_exit
	end

	if @action = 'add'
	begin
		-- VALIDATE COLUMN TYPE --
		if @typename not in (N'nchar',N'nvarchar',N'ntext',N'char',N'varchar',N'text', N'image')
		begin
			raiserror(15611,-1,-1,@colname,@tabname)
			goto error_abort_exit
		end

		-- LANGUAGE
		if @language is null
			begin
				-- USE THE SERVER DEFAULT WORD BREAKING LANGUAGE
				select @language = value from master.dbo.syscurconfigs where config = 1126
			end
		else
			begin
				-- VALIDATE @LANGUAGE ARGUMENT
				if @language < 0
				begin
					raiserror(15600,-1,-1,'sp_fulltext_column')
					goto error_abort_exit
				end
			end

		update syscolumns set language = @language where id = @objid and name = @colname

		-- IF TABLE HAS ZERO INDEXED COLUMNS (THIS IS THE FIRST COLUMN TO BE ADDED), MARK IT ACTIVE
		if not exists (select * from syscolumns where id = @objid and (colstat & 16) = 16)
			and (ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0)
			and (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 0)
		begin
			update sysobjects set status = (status  | 8) where id = @objid
		end

		-- SET THE BIT FOR THIS COLUMN --
		update syscolumns set colstat = colstat | 16 where id = @objid and name = @colname

		-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
		DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
		if @@error <> 0
			goto error_abort_exit

		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
		begin


			if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
			begin

				-- DELETE SYSFULLTEXTNOTIFY ENTRIES
				delete sysfulltextnotify where tableid = @objid

				-- START A FULL CRAWL FOR THE TABLE
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
				if @@error <> 0
					goto error_abort_exit
			end

		end
		else
		begin

			-- SET STATE TO SCHEMA_MODIFIED
			update sysobjects set status = ((status & ~64) | 128) where id = @objid
		end

		if @typename = N'image'
		begin
			-- VALIDATE THAT THE TYPE COLUMN IS GIVEN AND THAT IT IS VALID
			if @type_colname is null
			begin
				raiserror(15600, -1, -1, 'sp_fulltext_column')
				goto error_abort_exit
			end

			declare @typecolname sysname
			select @typecolname = type_name(ColumnProperty(@objid, @type_colname, 'SystemType'))

			-- TYPE COLUMN HAS TO BE A CHARACTER COLUMN
			if @typecolname not in (N'nchar',N'nvarchar',N'char',N'varchar')
			begin
				raiserror(15600 , -1, -1, 'sp_fulltext_column')
				goto error_abort_exit
			end

			-- ADD ENTRY OF COLID IN SYSDEPENDS
			declare @colid smallint
			declare @type_colid smallint

			select @colid = colid from syscolumns where [id] = @objid and name = @colname
			select @type_colid = colid from syscolumns where [id]  = @objid and name = @type_colname

			if not exists ( select [id] from sysdepends
							where  [id] = @objid and
								   depid = @objid and
								   number = @colid )
			begin
				insert into sysdepends ([id], depid, number, depnumber, status, deptype )
						values( @objid, @objid, @colid, @type_colid, 0, 1)
			end

			-- SET BIT INDICATING TYPE COLUMN
			update syscolumns set colstat = colstat | 64 where id = @objid and name = @type_colname
		end

	end
	else
	begin
		-- CLEAR THE BIT & ZERO LCID FOR THIS COLUMN --
		update syscolumns set colstat = colstat & ~16, language = 0
			where id = @objid and name = @colname

		-- IF LAST COLUMN DROPPED
		if not exists (select * from syscolumns where id = @objid and (colstat & 16) = 16)
		begin
			-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
			if @@error <> 0
				goto error_abort_exit

			-- IF TABLE HAS NOT BEEN DEACTIVATED
			if ((ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1)
				or (ObjectProperty(@objid, 'TableIsFulltextSchemaModified') = 0))
			begin

				-- IF CHANGE-TRACKING IS OFF
				if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 0
				begin
					-- SET TABLE TO SCHEMA UNMODIFIED, INACTIVE (TURN OFF ALL BITS)
					update sysobjects set status = (status & ~200) where id = @objid
				end
				else
				begin
					-- SET TABLE TO INACTIVE
					update sysobjects set status = (status & ~8) where id = @objid
				end
			end
		end
		else
		if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
		begin

			-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
			if @@error <> 0
				goto error_abort_exit

			if ObjectProperty(@objid, 'TableHasActiveFulltextIndex') = 1
			begin
				-- DELETE SYSFULLTEXTNOTIFY ENTRIES
				delete sysfulltextnotify where tableid = @objid

				-- START A FULL CRAWL FOR THE TABLE
				DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
				if @@error <> 0
					goto error_abort_exit
			end

		end
		else
		begin

			-- STOP A FULL/INCREMENTAL POPULATION FOR THIS TABLE --
			DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 2 )
			if @@error <> 0
				goto error_abort_exit

			-- SET BITS IN SYSOBJECTS
			update sysobjects set status = ((status & ~64) | 128) where id = @objid
		end

		-- IF IMAGE COLUMN, UNBIND FROM THE TYPE COLUMN
		if @typename = N'image'
		begin
			declare @colid1 smallint
			declare @type_colid1 smallint

			select @colid1 = colid from syscolumns where [id] = @objid and name = @colname
			select @type_colid1 = depnumber from sysdepends
					where [id] = @objid and
						 depid = @objid and
						 number = @colid1

			delete sysdepends where [id] = @objid and
									depid = @objid and
									number = @colid1 and
									depnumber = @type_colid1 and
									deptype = 1

			-- CLEAR BIT RELATING THE IMAGE COLUMN AND TYPE COLUMN
			if not exists ( select depnumber from sysdepends 
							where	[id] = @objid and
									depnumber = @type_colid1 and
									deptype = 1 and
									number in (select colid from syscolumns 
											 where	[id] = @objid and
													type = 34 and
													(colstat & 16) = 16) )
			begin
				update syscolumns set colstat = colstat & ~64 where [id] = @objid and colid = @type_colid1
			end
		end

	end

	-- COMMIT TRAN --
	commit tran
	if @@error <> 0
		goto error_abort_exit

	-- SUCCESS --
	return 0

error_abort_exit:
	rollback tran
	return 1	-- sp_fulltext_column
go

create procedure sp_helptext
@objname nvarchar(776)
,@columnname sysname = NULL
as

set nocount on

declare @dbname sysname
,@BlankSpaceAdded   int
,@BasePos       int
,@CurrentPos    int
,@TextLength    int
,@LineId        int
,@AddOnLen      int
,@LFCR          int --lengths of line feed carriage return
,@DefinedLength int

/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/
,@SyscomText	nvarchar(4000)
,@Line          nvarchar(255)

Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
                             trailing blank spaces*/
CREATE TABLE #CommentText
(LineId	int
 ,Text  nvarchar(255) collate database_default)

/*
**  Make sure the @objname is local to the current database.
*/
select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
        begin
                raiserror(15250,-1,-1)
                return (1)
        end

/*
**  See if @objname exists.
*/
if (object_id(@objname) is null)
        begin
		select @dbname = db_name()
		raiserror(15009,-1,-1,@objname,@dbname)
                return (1)
        end

-- If second parameter was given.
if ( @columnname is not null)
    begin
        -- Check if it is a table
        if (select count(*) from sysobjects where id = object_id(@objname) and xtype in ('S ','U ','TF'))=0
            begin
                raiserror(15218,-1,-1,@objname)
                return(1)
            end
        -- check if it is a correct column name
        if ((select 'count'=count(*) from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) =0)
            begin
                raiserror(15645,-1,-1,@columnname)
                return(1)
            end
    if ((select iscomputed from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) = 0)
		begin
			raiserror(15646,-1,-1,@columnname)
			return(1)
		end

        DECLARE ms_crs_syscom  CURSOR LOCAL
        FOR SELECT text FROM syscomments WHERE id = object_id(@objname) and encrypted = 0 and number =
                        (select colid from syscolumns where name = @columnname and id = object_id(@objname) and number = 0)
                        order by number,colid
        FOR READ ONLY

    end
else
    begin
        /*
        **  Find out how many lines of text are coming back,
        **  and return if there are none.
        */
        if (select count(*) from syscomments c, sysobjects o where o.xtype not in ('S', 'U')
            and o.id = c.id and o.id = object_id(@objname)) = 0
                begin
                        raiserror(15197,-1,-1,@objname)
                        return (1)
                end

        if (select count(*) from syscomments where id = object_id(@objname)
            and encrypted = 0) = 0
                begin
                        raiserror(15471,-1,-1)
                        return (0)
                end

        DECLARE ms_crs_syscom  CURSOR LOCAL
        FOR SELECT text FROM syscomments WHERE id = OBJECT_ID(@objname) and encrypted = 0
                ORDER BY number, colid
        FOR READ ONLY
    end

/*
**  Else get the text.
*/
SELECT @LFCR = 2
SELECT @LineId = 1


OPEN ms_crs_syscom

FETCH NEXT FROM ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0
BEGIN

    SELECT  @BasePos    = 1
    SELECT  @CurrentPos = 1
    SELECT  @TextLength = LEN(@SyscomText)

    WHILE @CurrentPos  != 0
    BEGIN
        --Looking for end of line followed by carriage return
        SELECT @CurrentPos =   CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)

        --If carriage return found
        IF @CurrentPos != 0
        BEGIN
            /*If new value for @Lines length will be > then the
            **set length then insert current contents of @line
            **and proceed.
            */
            While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
            BEGIN
                SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
                INSERT #CommentText VALUES
                ( @LineId,
                  isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                SELECT @Line = NULL, @LineId = @LineId + 1,
                       @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
            END
            SELECT @Line    = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
            SELECT @BasePos = @CurrentPos+2
            INSERT #CommentText VALUES( @LineId, @Line )
            SELECT @LineId = @LineId + 1
            SELECT @Line = NULL
        END
        ELSE
        --else carriage return not found
        BEGIN
            IF @BasePos <= @TextLength
            BEGIN
                /*If new value for @Lines length will be > then the
                **defined length
                */
                While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
                BEGIN
                    SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
                    INSERT #CommentText VALUES
                    ( @LineId,
                      isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                    SELECT @Line = NULL, @LineId = @LineId + 1,
                        @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
                END
                SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
                if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
                BEGIN
                    SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
                END
            END
        END
    END

	FETCH NEXT FROM ms_crs_syscom into @SyscomText
END

IF @Line is NOT NULL
    INSERT #CommentText VALUES( @LineId, @Line )

select Text from #CommentText order by LineId

CLOSE  ms_crs_syscom
DEALLOCATE 	ms_crs_syscom

DROP TABLE 	#CommentText

return (0) -- sp_helptext
go

grant execute on sp_droplogin to public
grant execute on sp_addsrvrolemember to public
grant execute on sp_dropsrvrolemember to public
grant execute on sp_grantdbaccess to public
grant execute on sp_addalias to public
grant execute on sp_addrole to public
grant execute on sp_addapprole to public
grant execute on sp_change_users_login to public
grant execute on sp_fulltext_table to public
grant execute on sp_fulltext_column to public
grant execute on sp_helptext to public
go

--------------------------------------------------------------------------------
--	ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
--------------------------------------------------------------------------------

if (exists (select * from sysobjects
		where name = 'sp_tableswc' and type = 'P '))
	drop proc sp_tableswc
go

print 'creating sp_tableswc'
go

create procedure sp_tableswc(
			   @table_name		nvarchar(384)	= null,
			   @table_owner 	nvarchar(384)	= null,
			   @table_qualifier sysname	= null,
			   @table_type		varchar(100) = null)
as
	declare @databasename	sysname
	declare @qualprocname	nvarchar(141) /* 128 + '..sp_tables' */

	create table #sptables (
		TABLE_QUALIFIER sysname collate database_default null,
		TABLE_OWNER sysname collate database_default null,
		TABLE_NAME sysname collate database_default null,
		TABLE_TYPE	varchar(32) collate database_default null,
		REMARKS varchar(254) collate database_default null)

	declare databases CURSOR FOR
		select name from master..sysdatabases
		where name like @table_qualifier and name <> 'model' and has_dbaccess(name)=1
		for read only

	open databases
	fetch next from databases into @databasename
	while (@@FETCH_STATUS <> -1)
	begin
		if (charindex('%', @databasename) = 0)
		begin	/* skip dbnames w/wildcard characters to prevent loop */
			select @qualprocname = @databasename + '..sp_tables'
			insert into #sptables exec @qualprocname
				@table_name, @table_owner, @databasename, @table_type
		end
		fetch next from databases into @databasename
	end
	deallocate databases
	select * from #sptables
		order by 4, 1, 2, 3
go

grant execute on sp_tableswc to public
go

-- Since we are updating the contents of instcat.sql and therefore the contents
-- of the ODBC/OLEDB catalog procs, we need to update the SPROC version

update spt_server_info 
	set attribute_value = '8.00.375'
	where attribute_id = 500
go

--------------------------------------------------------------------------------
--	End of SQLDMO System Procedures (SQLDMO.SQL)
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
-- END OF FILE: Turn off marking of system objects.
--	DO NOT ADD ANYTHING AFTER THIS POINT
--------------------------------------------------------------------------------
exec sp_MS_upd_sysobj_category 2
go

exec sp_configure 'allow updates',0
go

reconfigure with override
go