October 23, 2008 at 2:58 pm
Maqsood Ahmad (10/23/2008)
I executed this commandDBCC CHECKDB('AlarmSwitch') WITH NO_INFOMSGS
It took about 3 minutes and then I got message 'Command(s) completed successfully.'
Its mean there is no problem with my DB. Am I right?
Yup. No problems there
You are right we should not rely on these values. In fact we should store this kind of info in our own tables but because this db access layer was desinged by an other team for us about five years ago (for SQL Server 200) and they used this approach.
Unfortunately you're going to have to change your app. The other team designed things in a really bad way that went against every recommendation and best practice there is regarding the system tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2008 at 11:41 pm
you're suffering another bad practice .....
and you'll have to pay the price to get rid of it.
Don't mess with system objects !
That's like: I'm sysadmin of the dbserver hosting our paycheck system, so I'll give myself a raise because I know which column in which table to update .... just because I can....
Someone will notice in the long run...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 20, 2009 at 6:39 am
Hi Maqsood,
Can you recreate the table at the time of dropping or creating a new field? Before that you have to move/copy the existing data to a temporary table and copy back after the table created. If that is the case colid will be in sequence and also no need to touch the system tables.
Shaiju C.K.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
February 10, 2012 at 6:01 am
I need to Automatically Update the system Catalogues on 2008.
Basically I want Automatically remove the extended properties for a given table,
Now I know this can't be done, is there another way around this?
Exec sp_configure 'allow updates', 1 Reconfigure with override
waitfor delay '00:00:01'
DELETE
from sys.extended_properties
WHERE major_id =446624634
Exec sp_configure 'allow updates', 0 Reconfigure with override
February 10, 2012 at 6:07 am
Forget my last post
I can use the system Stored Procedures to do this.
February 10, 2012 at 6:08 am
Nope, system tables can't be modified in SQL 2008.
The way to drop extended properties is with
EXEC sp_dropextendedproperty.
The details of what the property name is and the objects its on can be found from sys.extended_properties, it'll probably need joining to sys.tables and maybe sys.columns.
What you can do is write some SQL querying sys.extended_properties and related tables and get it to generate and even execute the appropriate sp_dropextendedproperty statements.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2015 at 8:20 am
I need to change datatypes of multiple columns within multiple tables. How can I do this? Is there any stored procedure that will allow me to change on sys.types table?
Thanks.
December 9, 2015 at 8:34 am
ramadesai108 (12/9/2015)
I need to change datatypes of multiple columns within multiple tables. How can I do this? Is there any stored procedure that will allow me to change on sys.types table?Thanks.
Did you read this thread before you posted this? Modifying system tables does not work and is not recommended.
You can certainly use the system tables or views to generate code to do this.
The code below should get you started.
SELECT 'ALTER TABLE [' + object_name(C.object_id) + '] ALTER COLUMN [' + C.name + '] int ' + CASE WHEN C.is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END + CHAR(10) + 'GO'
FROM sys.columns C
INNER JOIN sys.types T ON C.system_type_id = T.system_type_id
WHERE T.name = 'bigint'
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 9, 2015 at 8:55 am
That will work, thanks.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply