Solved

Clean-up stored procedure

Posted on 2007-03-29
5
436 Views
Last Modified: 2013-11-15
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
0
Comment
Question by:kw_uh97
  • 3
5 Comments
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 50 total points
ID: 18817757
Other than tables with a domaiID column, how do you know which tables are in a domain?
0
 
LVL 9

Accepted Solution

by:
dduser earned 75 total points
ID: 18817916
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
 

Author Comment

by:kw_uh97
ID: 18818310
I will consider your advise. I will get back to you tomorrow on this.
0
 

Author Comment

by:kw_uh97
ID: 18860540
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
 

Author Comment

by:kw_uh97
ID: 18900381
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now