## SQLWays - Microsoft SQL Server Version Differences

Microsoft SQL Server versions are compatible in most ways. This chapter describes differences between Microsoft SQL Server 2000, 7.0 and 6.5 versions.

- bigint data type
- INFORMATION_SCHEMA.ROUTINES view

### bigint data type

Requirements: Microsoft SQL Server 2000 or later

The bigint data type allows you to store integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

The bigint data type is intended for special cases where the integer values may exceed the range supported by the int data type. The int data type remains the primary integer data type in SQL Server.

### INFORMATION_SCHEMA.ROUTINES view

Requirements: Microsoft SQL Server 2000 or later

You can use the INFORMATION_SCHEMA.ROUTINES view to retrieve information about stored procedures. This view contains one row for each stored procedure accessible to the current user in the current database.

For example, the following query returns owner, name and definition text of stored procedures in the current database:

    select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
    from INFORMATION_SCHEMA.ROUTINES
    where ROUTINE_TYPE='PROCEDURE'

The INFORMATION_SCHEMA.ROUTINES view was introduced in SQL Server 2000. This view is based on the sysobjects, syscomments and other system tables.

To retrieve the same information about stored procedures from a Microsoft SQL Server 7.0 database, you can use the following query:

    select su.name, so.name, sc.text
    from sysobjects so, syscomments sc, sysusers su
    where xtype='P' and so.id=sc.id and so.uid=su.uid
    order by su.name, so.name, sc.colid

The query above may return several rows per each stored procedure if the definition text is longer than 4000 characters.

[Main] [Table of Contents]
Copyright © 1999-2003 Ispirer Systems. All Rights Reserved.
SQLWays™ www.ispirer.com.