Use this command-line utility wherever accounts reside
Microsoft expects to ship SQL Server 7.0 this year (we received our copies of beta 3 midsummer). We're not going to focus exclusively on SQL Server 7.0 from now on (some of our best friends maintain sites that are running SQL Server 4.x), but we want to point out some new features we're jazzed about. (For more information about SQL Server 7.0, see Michael Otey, "What's New in SQL Server 7.0?" September 1998.)
This fall, Duke Communications is planning to launch a new publication, SQL Server Magazine, devoted exclusively to the needs of the SQL Server community. Get a peek at what's in store by visiting the new magazine's Web site at http://www.sqlmag.com.
Q: Will SQL Server 7.0 simplify obtaining schema data?
Current versions of SQL Server don't provide some simple commands (how many of you have yearned for a LIST TABLES command?). With Enterprise Manager (EM), you can drill down through the hierarchy (i.e., server group to server to database to tables), but the point-and-click approach tends to drive database administrators (DBAs) crazy. Microsoft has heard your plea and in SQL Server 7.0 provides new information schema views.
You're probably familiar with system tables, which contain metadata (i.e., data about data). All relational databases store information about themselves in a collection of nonstandard system tables. IBM's DB2 and Informix's Universal Server Universal Data Option servers call these tables system catalogs and Oracle calls them data dictionaries.
DBAs query these system tables to find details about a database's schema, configuration, and permissions. For example, SQL Server databases all have a sysobjects table containing a row of information about every object in the database. To obtain a list of all user ('U') tables in the current database, type
SELECT * FROM sysobjects
WHERE type = 'U'
Although system tables are handy, Microsoft has always discouraged users from writing queries directly against them because their structure is subject to change. However, lots of DBAs (and independent software vendors--ISVs) have ignored the warning, because they can glean so much useful information from the internal system tables.
SQL Server 7.0 will make giving up the system tables easier, thanks to the new information schema views that conform to the ANSI SQL-92 definition for the INFORMATION_SCHEMA. In other words, Microsoft has exposed an ANSI-standard map of the internal system tables. Instead of querying system tables directly, you'll query the appropriate information schema view to get information. We suspect nuggets of information will reside only in the system tables, but these new views will be adequate for most needs. Rather than selecting data directly from sysobjects to get a list of all tables in the database, you can run the following query:
SELECT * FROM information_schema.tables
Table 1, page 206, shows output from the SELECT* query when pubs is the active database. You can narrow your request (e.g., ask for only views or column names). You can find out more about the information schema views in Books Online (BOL); Figure 1 lists the schema catalogs you can request.
Finally, if you want to see how the competition handles system tables, take advantage of Platinum Technology's database tips (http://www.platinum.com/dbtips). You can download, view online, or order free posters of DB2's V4 or V5 mainframe system catalog or Oracle8's Data Dictionary. Platinum promises to post the SQL Server system tables soon. In the meantime, Steve Wynkoop has posted SQL Server 6.5 and SQL Server 7.0 system tables at http://www.swynk.com/friends/hotek/articles/ss65_schema.asp and http://www.swynk.com/friends/hotek/articles/ss70_schema.asp, respectively.
Q: Does SQL Server 7.0 finally give users an ALTER TABLE command that can change column definitions?
Yes, the old ALTER TABLE command didn't have an ALTER COLUMN option, a function that users expected to find. As you can see in the syntax from SQL Server 7.0 beta 3 Books Online (BOL), which Listing 1 shows, you can add or delete columns and their constraints. You can even disable triggers. Very cool. This functionality ranks right up there with true row-level locking on both data and index pages.