Explore SQL Server Database

For developers and analysts working with large databases, there is often a need to examine the structure of the database. In this article, we will show you how to do this using SQL queries.

Although we can use visual objects explorer to see the contents of our database, we can also use an SQL query to programming needs.

Let's see how to explore the SQL Server database using SQL queries.

Select detailed list of tables and views for current database

SELECT table_schema, table_name, column_name, data_type, character_maximum_length,
    is_nullable, column_default, numeric_precision, numeric_scale
FROM  information_schema.columns
ORDER BY table_schema, table_name, ordinal_position

Select information on particular table

We add the WHERE clause

SELECT table_schema, table_name, column_name, data_type, character_maximum_length,
    is_nullable, column_default, numeric_precision, numeric_scale
  FROM information_schema.columns
  WHERE table_name = 'TableName'
  ORDER BY table_schema, table_name, ordinal_position

Select the list of stored procedure for current database

SELECT *  FROM [DBName].information_schema.routines 
 WHERE routine_type = 'Procedure'

Select the list of functions for current database

SELECT *  FROM [DBName].information_schema.routines 
 WHERE routine_type = 'Function'