cs_uninstallscript.sql

community server 源码

/*

//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------


A SQL Server script to dynamically generate the cleanup script for Community Server

Runs best when run from Query Analyzer with the following options :

Tools | Options | Results
- Maximum characters per column (8000)
- Print column headers (unchecked)

Tools | Options | Connection Properties
- Set nocount (checked)

Query | Results To Text

Execute the query in your Community Server database to generate the drop script. Then copy the drop script into a new window and execute. 

All CS data will be removed. User information in the aspnet_ tables will still not be removed.


*/
-- GENERATE SCRIPT TO DROP ALL FOREIGN KEY CONSTRAINTS
select 
	'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = ''' + TABLE_NAME + ''' AND TABLE_SCHEMA = ''' + TABLE_SCHEMA + ''' AND CONSTRAINT_SCHEMA = ''' + CONSTRAINT_SCHEMA + ''' AND CONSTRAINT_NAME = ''' + CONSTRAINT_NAME + ''')' + CHAR(13) + 'BEGIN' + CHAR(13) +
	'  alter table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']' + CHAR(13) +
	'END' + CHAR(13) + CHAR(13)
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
where TABLE_NAME like 'cs_%' AND CONSTRAINT_TYPE = 'FOREIGN KEY'

SELECT 
	'IF EXISTS(SELECT * FROM sysobjects where type = ''TR'' and id = OBJECT_ID(N''' + u.name + '.' + o.name + '''))' + CHAR(13) + 'BEGIN' + CHAR(13) + 
	'  DROP TRIGGER [' + u.name + '].[' + tr.name + ']' + char(13) +
	'END' + CHAR(13) + CHAR(13)
from sysobjects tr 
	inner join sysobjects o on tr.parent_obj = o.id 
	inner join sysusers u on o.uid = u.uid 
where tr.type = 'TR' and o.name like 'cs_%'

-- drop all primary keys
select 
	'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = ''' + TABLE_NAME + ''' AND TABLE_SCHEMA = ''' + TABLE_SCHEMA + ''' AND CONSTRAINT_SCHEMA = ''' + CONSTRAINT_SCHEMA + ''' AND CONSTRAINT_NAME = ''' + CONSTRAINT_NAME + ''')' + CHAR(13) + 'BEGIN' + CHAR(13) +
	'  alter table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']' + CHAR(13) + 
	'END' + CHAR(13) + CHAR(13)
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
where TABLE_NAME like 'cs_%' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

-- drop all procedures
select 
	'IF EXISTS(SELECT * FROM sysobjects where type = ''P'' and id = OBJECT_ID(N''' + u.name + '.' + o.name + '''))' + CHAR(13) + 'BEGIN' + CHAR(13) + 
	'  DROP PROCEDURE [' + u.name + '].[' + o.name + ']' + CHAR(13) + 
	'END' + CHAR(13) + CHAR(13)
from
	sysobjects o 
	inner join sysusers u on o.uid = u.uid 
where o.type = 'P' and o.name like 'cs_%'

-- drop all views
SELECT 
	'IF EXISTS(SELECT * FROM sysobjects where type = ''V'' and id = OBJECT_ID(N''' + u.name + '.' + t.name + '''))' + CHAR(13) + 'BEGIN' + CHAR(13) + 
	'  DROP VIEW [' + u.name + '].[' + t.name + ']' + char(13) +
	'END' + CHAR(13) + CHAR(13)
from 
	sysobjects t
	inner join sysusers u on t.uid = u.uid 
where t.type = 'V' AND t.name like 'cs_%'

-- drop all cs tables
SELECT 
	'IF EXISTS(SELECT * FROM sysobjects where type = ''U'' and id = OBJECT_ID(N''' + u.name + '.' + t.name + '''))' + CHAR(13) + 'BEGIN' + CHAR(13) + 
	'  DROP TABLE [' + u.name + '].[' + t.name + ']' + char(13) +
	'END' + CHAR(13) + CHAR(13)
from 
	sysobjects t
	inner join sysusers u on t.uid = u.uid 
where t.type = 'U' AND t.name like 'cs_%'

-- drop any defaults from the CS columns on the aspnet_membership table
SELECT 'IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = ''D'' and d.name = ''DF_aspnet_membership_Approved'')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_Approved
END'

SELECT 'IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = ''D'' and d.name = ''DF_aspnet_membership_ForceLogin'')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_ForceLogin
END'

SELECT 'IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = ''D'' and d.name = ''DF_aspnet_membership_LastAction'')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_LastAction
END'

SELECT 'IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = ''D'' and d.name = ''DF_aspnet_membership_LastActivity'')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_membership_LastActivity
END'

SELECT 'IF EXISTS(select d.name, t.name from sysobjects t inner join sysobjects d on t.id = d.parent_obj where d.type = ''D'' and d.name = ''DF_aspnet_Membership_PasswordFormat'')
BEGIN
	ALTER TABLE aspnet_Membership DROP CONSTRAINT DF_aspnet_Membership_PasswordFormat
END'

-- drop the special columns in aspnet_Membership
SELECT 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''cs_UserID'' and TABLE_NAME = ''aspnet_Membership'' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_UserID]
END'

SELECT 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''cs_ForceLogin'' and TABLE_NAME = ''aspnet_Membership'' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_ForceLogin]
END'

SELECT 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''cs_UserAccountStatus'' and TABLE_NAME = ''aspnet_Membership'' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_UserAccountStatus]
END'

SELECT 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''cs_AppUserToken'' and TABLE_NAME = ''aspnet_Membership'' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_AppUserToken]
END'

SELECT 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''cs_LastActivity'' and TABLE_NAME = ''aspnet_Membership'' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_LastActivity]
END'

SELECT 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''cs_LastAction'' and TABLE_NAME = ''aspnet_Membership'' )
BEGIN
	alter table aspnet_Membership DROP COLUMN [cs_LastAction]
END'

SELECT 
'IF EXISTS(SELECT * FROM sysobjects where type = ''FN'' and name = ''GetAnonymousUserID'')' + char(13) + 
'begin' + char(13) + 
'	DROP FUNCTION GetAnonymousUserID' + char(13) + 
'end'

SELECT 
'IF EXISTS(SELECT * FROM sysobjects where type = ''FN'' and name = ''HasReadPost'')' + char(13) + 
'begin' + char(13) + 
'	DROP FUNCTION HasReadPost' + char(13) + 
'end'