Help getting rid of sql default tables
-
After an install of Sql 2000 Enterprise, I can see that Sql puts in all of these "Test" or "Development" tables. They have prefixes like sys (sysobjects,syscomments,etc...). I'm trying to clean up my companies 200+ gb database, and want these out of there. I've managed to delete some, but others are giving me errors? Any ideas?
Cheers,
Ryan
-
Looks like you are trying to delete SQL Server system tables. NOT a good idea. What tables have you been able to delete so far?
-
Keeping with the humorous nature of this thread... I couldn't stop laughing when I read this. Here's what you do....1. Post your resume on monster and start looking for a new job, preferably not as a DBA.
2. Walk into your bosses' office and tell him that you are a complete moron who should be nowhere near a SQL Server, let alone have sysadmin rights to one. Maybe your honesty will get you a severance package.
3. Go home and either read a book or a help file, or any web page that has anything to do with SQL Server, so you'll understand why you had to quit your job.
A.J.
DBA with an attitude -
You sound like a Jive Turkey. FYI, I am a senior DBA for a very successful Government Agency. I'm sorry I'm not "hip" to all your "secret" Sql tables. Maybe you don't need to be concerned with space on your dinky Sql installs, but I do.
-
LMAO!!!
Stop dude... I am going to die from laughter. I do believe you about working for the government though, probably the IRS (LMAO). "Senior DBA" eh? I would absolutely LOVE to interview you someday... for a job mowing my lawn.
A.J.
DBA with an attitude -
Dude, how did you become a senior DBA without knowledge of the existence of system tables? They have always existed. Even MS.Access has system tables, just hidden. Where have you been? You are likely to corrupt any/all databases you have removed the system tables from.
-
Ryan Moats = your tax $$ at work.
A.J.
DBA with an attitude -
You're a douche bag. I hope an admin sees how you treat new users on here. You're not giving this forum a good name in the least.
IMHO, you're worst than the foreign people who left these crazy scripts for me to run.
-
Well stop insulting me an help!!!! It's what you're paid to do!
-
What have you managed to delete so far? Have you successfully deleted these in all databases or is this isolated?
-
I don't know. The little foreign guy who was here "helping" last week gave me this script to run. He was deleting all table that had a tbl prefix before them. I just modified it to do it for "sys". Not sure what go wiped!!!!!
-
I am a douchebag... you ain't the first to tell me this. Run the code below in your oh-so-important government database to find out which tables / indexes are using up the most amount of space. Note this script only shows allocated space and not unallocated space. You can use this info and go from there to determine what data to archive or delete.... and stay away from the system tables since SQL Server needs those to function normally (that would be any table with a prefix of sys in SQL 2000).
SELECT OBJECT_NAME(si.id) AS [Table],
CAST(SUM(CASE WHEN si.indid IN (0,1) THEN si.dpages * 8/1024.00
ELSE 0
END) AS DECIMAL(10,2)) AS [Data (MB)],
CAST(SUM(CASE WHEN si.indid NOT IN (0,1) THEN si.dpages * 8/1024.00
ELSE 0
END) AS DECIMAL(10,2)) AS [Index (MB)],
CAST(SUM (si.dpages * 8/1024.00) AS DECIMAL(10,2)) AS [Total (MB)]
FROM sysindexes si
WHERE ISNULL(INDEXPROPERTY(si.id, si.name, 'IsStatistics'),0) = 0
AND ISNULL(INDEXPROPERTY(si.id, si.name, 'IsHypothetical'),0) = 0
AND OBJECT_NAME(si.id) = ISNULL(null, OBJECT_NAME(si.id))
GROUP BY si.id
ORDER BY OBJECT_NAME(si.id)
Or... if you really want to delete all the data from your system tables do a google search on sp_configure 'allow_updates'... although I'd bet my paycheck that things will go a tad badly for you if you were to do that.
Disclaimer: Myself and the company I work for take zero responsibility for the actions of Ryan Moats, due to his inability to understand sarcasm.
A.J.
DBA with an attitude -
The list of system tables:
sysobjects
sysindexes
syscolumns
systypes
syscomments
sysfiles1
syspermissions
sysusers
sysproperties
sysdepends
sysreferences
sysfulltextcatalogs
sysindexkeys
sysforeignkeys
sysmembers
sysprotects
sysfulltextnotify
sysfiles
sysfilegroups
-
Ryan,
Although what you are attempting to do here is quite shocking, I’m not sure that deserves a condescending response. It recommend that you restore your latest backup of your database to a temporary or test DB and recover your lost sys* tables by copying the non-existent tables back into your production DB. All SQL Server databases are created with these internal system tables that house critical data about your database. Open up Books Online and review the system tables overview. In the future, I suggest that you do a bit of research prior to dropping any table from a database if you don’t explicitly know the usage for the table. For example, if you were to look at the data contained within the sysobjects, syscomments, or sysindexes tables, you’d quickly see that is important system data. I hope your experience here has not altered your opinion of this site as I can attest that it has been an integral part of my knowledge gathering process over the last few years.
-
What did the script look like that you used?
Viewing 15 posts - 1 through 15 (of 61 total)
You must be logged in to reply to this topic. Login to reply