MS SQL Server Admin - Queries

Listed below are queries / stored procedure calls that can be used to get information on MS SQL Server objects such as tables, views, indexes, procedures, functions, triggers, schemas, and users.

Tables and Views

To get all tables, views, and system tables, the following SQL Server system stored procedure can be executed.

exec sp_tables '%'

To filter by database for tables only, for example master:

exec sp_tables '%', '%', 'master', "'TABLE'"

To filter by database and owner / schema for tables only, for example, master and dbo:

exec sp_tables '%', 'dbo', 'master', "'TABLE'"

To return only views, replace "'TABLE'" with "'VIEW'". To return only system tables, replace "'TABLE'" with "'SYSTEM TABLE'".

 

Schemas / Owners

Here are two examples for queries to get schema / owner information.

select distinct SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA order by SCHEMA_NAME

select name from dbo.sysusers where islogin = 1 order by name

 

Procedures

This is a query to get all MS SQL Server procedures.

exec sp_stored_procedures '%'

The query can be filtered to return procedures for specific schemas / owners and databases by appending more information onto the procedure call, such as the following:

exec sp_stored_procedures '%', 'dbo', 'master'

 

Procedure Columns

This is a system stored procedure call to get the columns in a SQL Server procedure.

exec sp_sproc_columns 'get_employee_names', 'dbo', 'sample'

 

Functions

This is a query to get all MS SQL Server functions.

select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES where upper(ROUTINE_TYPE) = 'FUNCTION'

 

Triggers

This is a query to get all MS SQL Server triggers.

select * from sysobjects where type = 'TR'

The query can be filtered to return triggers for a specific owner by appending a user_name call onto the where clause to the query.

select * from sysobjects where type = 'TR' and user_name(sysobjects.uid) = 'dbo'

 

Indexes

This is a query to get MS SQL Server indexes for a particular table. In this example, the table used is employee.

exec sp_helpindex 'employee'

Listed below are links to articles containing system queries for other databases:

Cassandra: Cassandra System Queries

Firebird: Firebird System Queries

MySQL: MySQL System Queries

Oracle: Oracle System Queries

PostgreSQL: PostgreSQL System Queries

SQLite: SQLite System Queries

Sybase: Sybase System Queries