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)