sysobjects - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sysobjects

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-07-13 : 19:54:07

Guys,

I am looking to update all my objects including columns, views etc to uppercase.
This is what I do to acheive the same

sp_configure 'allow updates',1
go
reconfigure with override

update syscolumns
set name=upper(name)
where id in(
select id from sysobjects
where xtype='U')

sp_configure 'allow updates',0
go
reconfigure with override

I do this for xtype = 'TF', 'V' etc

But the problem comes with the defintion of the view, it still remains in lower case

Is there any way by which I can print the text of the view and do upper(text). Like in Oracle I can query v$sqlarea is there any equivalent sys table in SQL SERVER which gives the defiNItion of the view so that I can update it to upper case.

Or is there any other way I can acheive what I want to do

Any suggestions.inputs are appreciated

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 19:55:46

I would highly recommend against updating system tables directly.

But anyway, syscomments contains the view code. Stored procedures and UDFs are stored in there as well.

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-07-13 : 19:58:06

Well, you DO NOT want to do it by updating the system tables. It's an easy way to break something and should not be attempted. Also, unless your database is set to case-sensitive collation, changing the column names to uppercase will make no difference.

The only way to change the view (and stored procedures, and user-defined functions) is to script them out to SQL files and make the changes there, then run the scripts to recreate them.

You should use the sp_rename procedure to rename a column. You'll still have to script out views, procs, etc. and change them.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-07-13 : 20:05:01

Guys,
The whole purpose of making my schema upper case is for the application code to be compatible with the schema objects across both oracle and sql server. As you know Oracle names the objects in default upper case.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-07-13 : 20:12:08

Compatibility is one thing, making an unnecessary change in a system that will not notice the difference is something else. You can program your app code in uppercase if you like, SQL Server will not be affected. The old saying, "if it ain't broke, don't fix it" applies.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources