Guides:SQL/How do I/Get Database and Table Information in a Microsoft SQL Server

From CoderGuide

Jump to: navigation, search

Return to the SQL How Do I Index

Contents

Stored Procedures

If, for some reason, you want a list of all stored procedures in your database:

exec sp_stored_procedures;

sp_stored_procedure is actually a stored procedure it's self, but just one built into the MS-SQL server. It can take any of the following opitons:

@sp_namereturn the stored procedure of just that name. Wildcards are supported (so 'sp%' would return all stored procedures starting with 'sp')
@sp_ownerList stored procedures belonging to the specified owner.
@qualifierSpecify what database to list stored procedures for.
@fUsePatternset to 1 if wildcard matching is to be turned on on (default), 0 if it is not.

Microsoft likes to change their internal database structure from time to time, so it is recommended you use the stored procedures wherever possible. For example, The following SQL queries can be used to get a list of all databases in a SQL server:

This works in SQL 2000, but not 2005:

SELECT * FROM master.sysdatabases

This works in 2005, but not 2000:

SELECT * FROM SYS.DATABASES

MySQL, and other open source projects, often aim not to break existing code. Microsoft has a habit of doing that from time to time (one upon a time, Visual Basic allowed you to have your arrays start at 1 instead of 0. This was later removed, breaking all old code that used this feature).

Tables

These commands use EXEC to execute stored procedures that will return various information about the SQL database.

To get a full list of tables on the Microsoft SQL server, use:

EXEC sp_tables;


sp_talbes can take the following parameters: @table_name, @table_owner, @table_qualifier (i.e. database name), @table_type, and @fUsePattern

To perform the MySQL equivalent of "DESCRIBE table" to get information on the structure of a table, use:

EXEC sp_columns YourTableName;

If you just want information on one column name, then:

EXEC sp_columns YourTableName, @column_name = 'YourColumnName';


Databases

To get a list of all databases, use

EXEC sp_databases;


References

Personal tools