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'