Getting list of tables, and fields in each, in a database

Unleashing the full power of catalog views

To quote Spider-Man, "With great power, comes great responsibility"... and querying database metadata is no exception to this rule! Two techniques that make you an overnight superhero are using Microsoft SQL Server's OBJECT CATALOG VIEWS (sys.objects, sys.columns, sys.types), and the INFORMATION_SCHEMA. Get ready to Web Sling your way through system objects!

Use the force (or this code snippet, whichever is easier):

SELECT o.name AS Table_Name,
       c.name AS Column_Name,
       t.name AS Data_Type,
       c.max_length AS Size,
       c.precision,
       c.scale
FROM sys.objects o    -- Consider sys.objects as your "council of tables".
JOIN sys.columns c ON o.object_id = c.object_id  -- sys.columns, on the other hand, is like VIP access to all the columns of tables. No red ropes here!
JOIN sys.types t ON c.user_type_id = t.user_type_id  -- sys.types gives us the low-down on data types.
WHERE o.type = 'U' -- 'U' stands for user tables, not for 'Underwear'. 
ORDER BY o.name, c.column_id; -- Good order is the foundation of all things. That's SQL Server speaking, not your mom.


A dive into information schema

Unleashing your full potential with INFORMATION_SCHEMA is like taking the red pill in the Matrix - suddenly, you're seeing the world (or in our case, metadata) for what it really is.

Take this spoon, err... code:

SELECT 
    COLUMN_NAME, 
    TABLE_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH, 
    NUMERIC_PRECISION, 
    NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'YourDbName' -- Replace with your database, unless it really is 'YourDbName'... again, we need to talk.
AND TABLE_NAME = 'YourTableName' -- If you have a specific table in mind, the world is your oyster.


A quick detour for ORM warriors

Working with ORM? Let's channel our inner Dora The Explorer - understanding the layout and configuration of tables can help automate ORM mappings.

For those yearning to master SQL Server functionality beyond just the basics, there are blogs and resources that are basically treasure maps, leading you to the goldmine of insights.

Good Practices Vs the Dark Side

Remember the Spider-Man quote? Well, with the capability of accessing metadata comes the responsibility to use it wisely. Don't go shooting web (or running queries) all over the place - filter your results and stay aware of potential performance impacts.

Also, while GROUP_CONCAT is a gem, it's not an Infinity Stone. It has a limit to the maximum string length it can return. Balance in all things, young padawan!

Precision - Key to the queries kingdom

Want to become the Sherlock Holmes of SQL queries? Make each query count - specify only the columns necessary. With power comes responsibilities, and with metadata comes insights into constraints, indexes, and triggers.