Posted by Erland Sommarskog on 10/01/77 11:22

serge (sergea@nospam.ehmail.com) writes:
> I tried all the INFORMATION_SCHEMA on SQL 2000 and
> I see that the system tables hold pretty much everything I am
> interested in: Objects names (columns, functions, stored procedures, ...)
> stored procedure statements in syscomments table.
>
> My questions are:
>
> If you script your whole database everything you end up having
> in the text sql scripts, are those also located in the system tables?

Scripting does not place anything in system tables. Creating objects
does. The source code of stored procedures, views, functions, constraints
and a few more objects are in syscomments. However, they are stored in
8000 character slices, and non-trivial to decode.

Tables, indexes and user-defined types are not stored as text as such
in SQL Server, but as scattered pieces which the scripting tools
reassemble.

In any case, you should not really script your databases more than at
most once. View the database as a storage for binary objects, and keep
your source code under version control. (But if you started without
version control, you may need to script once to get yourself a baseline.)

> I read many places that Microsoft
> says not to modify anything in those tables and not query them since their
> structure might change in future SQL versions.
>
> Is it safe to use and rely the system tables?

As David said, there are new means for retrieving meta data in SQL 2005,
and the old system tables in SQL 2005 become views implemented on top
of these, known as "Compatibility views". They are also marked as
deperecated, and they will surely disappear from a future version of
SQL Server, but this is not likely to happen this decade.

Important is to only use documented columns, and only use columns what
they are documented for. A column which has the short description of
"Reserved", will very likely always have a value of 0 or NULL in SQL 2005.

Personally, I use only the system tables for meta-data access in SQL 2000,
and this is also what I recommend. The INFORMATION_SCHEMA views are not
whole-covering, so mixing both means that you must know two paradigms.
There are also some traps with the views which can cause you to not
get data you expect, or columns may have names which makes promises
they don't live up to. SQL Server MVP Tony Rogerson also liks to point
out that they have scalability problems. Finally, all that uppercase
is ugly. :-)


> I basically want to do at least fetching of information i want from the
> system tables rather than the SQL script files.
> I also want to know if it's pretty safe for me to make changes in these
> tables.

Never make changes directly to system tables, unless you are told to so
by a Microsoft support professional.

> Can i rename an object name for example an Index name, a Stored Procedure
> name?

To rename an index use sp_rename. To rename a stored procedure, drop
the procedure and reload it under the new name.

> Can i add a new column in the syscolumns table for a user table?

Absolutely not! You will corrupt your database.

Please keep in mind that there is a whole lot of internal structures
in SQL Server that are not exposed. When you add a column to a table,
SQL Server may need to update all pages in the tables, to create space
for the column. Query plans for procedures that has SELECT * may need
to be flushed etc.

In fact, in SQL 2005, you don't even have read access to the real system
tables. All you see is views.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]