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, sometimes it is faster to run the queries.

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

Get 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

Get information on particular table.

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

Get the list of stored procedures for current database.

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

Get the list of functions for current database.

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