DEV Community 👩‍💻👨‍💻

Cover image for SQL-Quick tip #10 - Select table definition
Allan Simonsen
Allan Simonsen

Posted on

SQL-Quick tip #10 - Select table definition

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Select table definition

Have you ever had the task of documenting a whole database or maybe just a few tables some programmer created years ago. I have, and I quickly found that having a query to directly select the schema of a table was a huge timesaver.
Or maybe you don't have access to the production database, but the bugs or performance issues you experience, indicate a missing index or a wrong column definition, so you need to send a request for the IT operations department to fetch the information about a table from the database for you.
In both cases the queries below can be very useful.

DECLARE @tableName VARCHAR(100) = 'Customers'

SELECT table_name, ordinal_position, column_name, data_type, character_maximum_length, is_nullable
  FROM information_schema.columns
 WHERE table_name = @tableName

SELECT table_name, constraint_name
  FROM information_schema.constraint_table_usage
 WHERE table_name = @tableName

SELECT name, type_desc, is_unique, is_primary_key
  FROM sys.indexes
 WHERE object_id = OBJECT_ID(@tableName)
Enter fullscreen mode Exit fullscreen mode

Sql Server Management Studio screenshot

Top comments (0)

Thank you.

Thanks for visiting DEV, we’ve worked really hard to cultivate this great community and would love to have you join us. If you’d like to create an account, you can sign up here.