"clever" way to remove un-used tables
-
Hi,
I want to:
1)clean-up my database
2)remove all un-used userTables.
At the moment, I have a sproc which I pass the "userTableName" to.
This sproc lists the sprocs that use this userTable.
This still seems a bit "manual" to me.
eg,in QA, I have to:
1) run the sproc for each table in the database.
2) copy and paste the userTableName into the sproc arg in QA.
3) append the text "del" onto the end of the tableName,
(so that I know that this table should be deleted)
..this is all off the top of my head..I don't know any better 🙂
can anyone suggest cleverer ways, eg maybe I could create a sproc that:
1) lists all the userTables that are not referenced in any sprocs
2) appends the text "del" onto the end of these userTables
3) pipes them off to a "PENDING DELETE" folder,
thereby reducing "clutter" when I browse my "userTables" in EM.
I hope this is a clear post.
pls let me know if i can clarify.
cheers,
yogiberr
-
sysobjects table has parent_obj column should help you.
-
Hi,
I looked into the parent_obj.
<sql>
SELECT *
FROM sysobjects
WHERE xtype = 'U'
<\sql>
All the "parent_obj" = 0
I'm not really sure how the "parent_obj" will be able to help me.
Should I somehow be able to run a query that will list all the tables that are not referenced in any of the sprocs?
sorry for silly question,
cheers,
yogi
-
Here is a query that will return the list of tables and the Sp that reference them:
SELECT DISTINCT TABLE_NAME
, object_name(id)
from syscomments,
INFORMATION_SCHEMA.TABLES
where text like '%' + TABLE_NAME + '%'
Combine a slight variation on this with a query that selects all table names, as follows, and you have the set of tables that are not referenced by any stored procs. Now come the possible issues...
You will need to extend this simple version to check for tables references by views, constraints, checks, etc. It also doen't catch any tables referenced directly from higher level code such as VB or JAVA. But we never have developers that embed table names directly in Java, do we? Anyway, here is a query that identifies all tables not referenced in stored procedures:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT IN (SELECT DISTINCT TABLE_NAME
from syscomments,
INFORMATION_SCHEMA.TABLES
where text like '%' + TABLE_NAME + '%'
)
Hope this helps
Wayne
-
Cheers Wayne.
I hadn't thought about views and constraints etc.I'll have a look and see if I can work that into the query
ta,
yogiberr
-
Although it is a good idea not to use the system tables the sysdepends table holds the information you are looking for. Consider the following...
SELECT DISTINCT ObjectName = so.name
, ObjectType = CASE so.xtype
WHEN 'U' THEN 'table'
WHEN 'P' THEN 'stored procedure'
WHEN 'TR' THEN 'trigger'
WHEN 'V' THEN 'view'
WHEN 'FN' THEN 'function'
WHEN 'IF' THEN 'function'
WHEN 'TF' THEN 'function'
else so.xtype
end
, DependentTable = sod.name
FROM sysobjects so
JOIN sysdepends sd ON so.id = sd.id AND so.status>=0
JOIN sysobjects sod ON sd.depid = sod.id AND sod.xtype = 'U'
This should give you all the tables that don't have dependencies and where the dependencies are. The following query should just show the tables that don't have any dependencies (with the exception of DTS Packages).
SELECT TablesWNoDependencies = name
FROM sysobjects s
WHERE NOT EXISTS(
SELECT 1
FROM sysdepends sd
JOIN sysobjects sod ON sd.depid = sod.id AND sod.xtype = 'U'
WHERE s.name = sod.name
)
AND s.xtype = 'U'
AND s.status >=0
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
sweet, thanks Gary.
Gary<<it is a good idea not to use the system tables>>
yogi<< why is that?Is it incase sql server changes the way that they store syssobjects in the future?>>
ta,
yogi
-
yogi<< why is that?Is it incase sql server changes the way that they store syssobjects in the future?>>
Exactly. I know that I have read there were changes to the system tables in Win2K Sp3 for instance. So you just don't know if they will get changed and in fact Microsoft specifically warns you not to use them for just this problem.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
I didn't know that.
more grief, ah well...I can't say you didn't warn me
yogi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply