DEV Community

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

Posted on

3 3

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

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay