Clean-up stored procedure

My central server (MS SQL Server 2000) is currently hosting a number of projects which are no longer in use, have been cancelled, or are hosted elsewhere. I need a process to clean-out obsolete data.

The scope is to prepare a stored procedure which takes a domain ID (usually a 4 char srting) as input parameter and cleans out all database tables for that domain.

There are several tables where the Domain ID stored. Any advise for starters. Let me know if DDL and sample data is needed.
Thanks In Advance for any help
kw_uh97Asked:
Who is Participating?
 
dduserCommented:
You can get table names from sysobject Table. You can store all the table name with domain id in temporary table and use them within a cursor to drop them.

Regards,

dduser
0
 
dqmqCommented:
Other than tables with a domaiID column, how do you know which tables are in a domain?
0
 
kw_uh97Author Commented:
I will consider your advise. I will get back to you tomorrow on this.
0
 
kw_uh97Author Commented:
Sorry did not mean to temporarily abandoned this question. I just got side tracked for a moment.

Well lets say that the table are less likely to be domain specific than the the tables having a domainID column. I can get all the tables with the DomainID column from the sysobjects, moreover I can concatenate delete statements using a cursor to all the tables as well. What I don't know is how would I find out which tables need to be deleted first due to one to many realtionships? Is there a way to find out which tables need to be deleted first?

Here is my code to get the the delete statements:

DECLARE @DBName AS Varchar(100)
DECLARE @Tables AS Varchar(100)
DECLARE @DomainList AS Varchar(100)

SET @DBName = 'PIMS_20070211'
SET @Tables = '%'
SET @DomainList = '''QAG3'',''NBEL'',''QGTL'',''ICET'',''DLNG'''

DECLARE @ObjectName as Varchar(100)
DECLARE @SQL AS Varchar(4000)
Declare @TableList Varchar(4000)

DECLARE cTables CURSOR FOR
SELECT Name FROM dbo.sysobjects
WHERE Name LIKE @Tables


OPEN cTables
FETCH NEXT FROM cTables INTO @ObjectName

WHILE @@FETCH_STATUS = 0
BEGIN


      SET @SQL = 'DELETE FROM ' + @DBName + '..' + @ObjectName
      IF EXISTS (SELECT * FROM stbv_DBColumns WHERE ColumnName='Domain' AND ObjectName=@ObjectName)
      BEGIN
            SET @SQL = @SQL + ' WHERE Domain IN ('+ @DomainList + ')'
      print @SQL
      END
   FETCH NEXT FROM cTables INTO @ObjectName
END

CLOSE cTables
DEALLOCATE cTables


Thanks In Advance for any help!
0
 
kw_uh97Author Commented:
Hey found my answer at this link http://www.microsoft.com/india/msdn/articles/Forming%20the%20Database%20Hierarchy.aspx

CREATE Procedure DB_Form_Tree_View (@TName Varchar(50)= NULL)
AS
BEGIN
                SET NOCOUNT ON

                SELECT @TName = ISNULL(@TName , '%')                

                -- Create a temporary table to store all the information about the hierarchy
                -- we are going to create  
                CREATE TABLE #Build_Hierarchy (rank INT, [id] INT, [name] VARCHAR(50) null)
                DECLARE @rank INT,  
                                @notfinished INT  
                 -- Create the variables you require for this procedure
                SELECT @rank = 0, @notfinished = 1
                -- Get tables that have no foreign key
                -- Get the first level have to be build to start off with
                INSERT INTO #Build_Hierarchy (rank, [id], [name])
                -- This query gets you the tables that donot reference any tables
                -- These can be considered as the Top level tables
                -- Potentially these are the table would be referenced by other tables
                SELECT @rank, [id], name FROM sysobjects obj WHERE not exists
                (SELECT fkeyid FROM sysreferences WHERE fkeyid = [id]) and type = 'U'
                and Upper(name) like Upper('%' + @TName + '%')

                -- Loop till you run out of child tables
                WHILE (@notfinished <> 0)
                BEGIN
                                -- Defines the Level you are searching ...
                                -- 0 Refers to the Top level and 1 Refers to the First level
                                -- and so on ...
                                SET @rank = @rank + 1
                                -- Get tables that have a foreign key reference to
                                -- tables already in our build order table.
                                INSERT INTO #Build_Hierarchy(rank, [id], name)
                                SELECT DISTINCT @rank, sr.fkeyid, object_name(sr.rkeyid)
                                FROM sysreferences sr
                                INNER JOIN #Build_Hierarchy bo
                                ON sr.rkeyid = bo.[id]
                                                WHERE bo.rank = @rank - 1 and
                                                NOT EXISTS
                                                (SELECT * FROM sysreferences sr1 WHERE sr1.fkeyid = sr.fkeyid
                                                and sr1.rkeyid NOT IN
                                                (SELECT [id] FROM #Build_Hierarchy WHERE rank <= @rank - 1))

                                -- Check if we have run out of child tables and exit
                                SELECT @notfinished = @@rowcount  
                end  

-- Select out the whole
SELECT DISTINCT rank as [Rank], Name as Parent,
CASE object_name([id]) WHEN Name then '-' ELSE object_name([id]) END as Child
FROM #Build_Hierarchy WHERE
name not like 'SYS%'
ORDER BY rank, Parent, Child

-- Cleanup Work
DROP TABLE #Build_Hierarchy
end

GO

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.