The design document is missing, but the database still knows its columns. Column definitions live in catalog views you can query before changing a table.
Start With the Object
Confirm the database and schema before querying a column. A name such as Orders can exist in more than one schema. The query window can also point to the wrong database. I check DB_NAME and the two part object name before trusting a catalog result.
sys.objects lists schema scoped objects. sys.columns lists columns for user objects visible to your account. Join them on object_id. Filter to tables when you want a table report. The catalog returns current metadata, not an old diagram or an application assumption.
What if your query returns no rows? Check the database, schema, object name, and permissions before concluding the column does not exist. Metadata visibility can hide objects that your account cannot access. Silence from a catalog query needs context.
SELECT
DB_NAME() AS CurrentDatabase,
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS TableName,
c.column_id,
c.name AS ColumnName
FROM sys.objects AS o
JOIN sys.columns AS c
ON c.object_id = o.object_id
WHERE o.type = 'U'
ORDER BY SchemaName, TableName, c.column_id;Join to the Type Name
sys.columns stores type identifiers, length, precision, scale, and nullability. Join user_type_id to sys.types to show the declared type name. That matters for alias types. Joining only on system_type_id can multiply rows because several user types share one underlying type.
The max_length column is in bytes. For nvarchar, character count is different from byte count. A value of minus one denotes a max type. Decimal precision and scale have their own columns. Do not format all types with the same length rule and call it a faithful CREATE TABLE script.
I use the raw catalog values first. A pretty report can come later. The raw values prevent a display conversion from hiding a detail that matters during a migration.
SELECT
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS TableName,
c.name AS ColumnName,
t.name AS TypeName,
c.max_length,
c.precision,
c.scale,
c.is_nullable
FROM sys.objects AS o
JOIN sys.columns AS c
ON c.object_id = o.object_id
JOIN sys.types AS t
ON t.user_type_id = c.user_type_id
WHERE o.type = 'U'
ORDER BY SchemaName, TableName, c.column_id;Read the Flags in Column Definitions Before Editing
A column can be computed, identity, sparse, hidden, or generated for a feature. Check the flags relevant to your server version. The basic name and type report is not a complete table definition. A rushed change can break an application when a generated column was mistaken for ordinary data.
sys.computed_columns holds the expression for computed columns. sys.identity_columns describes identity behavior. Default constraints and check constraints live in their own views. The catalog is a set of related views, not one giant answer column.
When I review an unfamiliar table, I inspect the special columns before writing an ALTER statement. The two minute check is cheaper than learning the constraint name from an error during deployment.
Use sys.all_columns for System Objects
sys.all_columns combines user and system object columns. sys.columns focuses on user objects. If you are studying the shape of a system object, the all view is the right starting place. Join it to sys.all_objects so the object name is visible beside the column.
Do not use sys.all_columns for an application schema report without filtering. System metadata will fill the result and hide the tables you came to inspect. Pick the view that matches the question.
This query shows a sample of catalog columns in the current database. It is a discovery query, not a guarantee that every internal implementation detail is exposed. Microsoft documents catalog views as the supported metadata surface.
SELECT TOP (50)
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS ObjectName,
c.column_id,
c.name AS ColumnName
FROM sys.all_objects AS o
JOIN sys.all_columns AS c
ON c.object_id = o.object_id
ORDER BY SchemaName, ObjectName, c.column_id;Check the Exact Table Name Before Reading Column Definitions
A broad report helps explore. For a production change, narrow the query to one object_id and verify the schema. OBJECT_ID accepts a two part name in the current database. If it returns NULL, do not guess a different table from a similar name.
Ask whether the column is referenced by indexes, constraints, computed columns, or modules. A type change can affect more than storage. The column definitions query is the first step in a dependency check, not the last.
Keep the catalog output with the change ticket when a column alteration is approved. It gives the reviewer the actual before state. A copied CREATE TABLE statement from an old repository can drift from the deployed table.
Mind Permissions and Scope
Catalog visibility follows permissions. A developer account can see fewer objects than a DBA account. If two people get different results, compare their database context and metadata permissions. Do not solve the discrepancy by granting broad server rights without a reason.
Temporary tables live in tempdb, with generated internal names. Cross database objects require querying the right database context. A three part reference in application code does not make the current database catalog show the remote table’s columns. Connect to or change context to the database that owns the object.
I state the database and principal when sharing a catalog query result. That helps another DBA reproduce it instead of arguing over two incomplete lists.
Turn Column Definitions Into Useful Documentation
Generate a dated schema report from the catalog. Include schema, table, column order, declared type, length, scale, nullability, and relevant flags. Add business descriptions from extended properties where your team maintains them. The catalog supplies structure; people supply meaning.
Refresh the report after deployment. A document generated once and forgotten becomes a confidence trap. Put the query in your repeatable build or review process so a reader can regenerate it from the current database.
The database is the final authority for its own column definitions. Start there, then compare the design document against reality.
For a precise change review, capture the column default and computed expression from their dedicated catalog views too. A nullable flag alone cannot explain what a new row receives when the application omits a value. Check index membership before widening a type, because a key size limit or application parameter can become the next problem. I keep these checks beside the column report so the change reviewer sees structure and behavior together.
Related reading on this blog: Scripts to Retrieve Column Names and Checking If a Column Exists in a Table.
Column documentation is not a missing PDF, it is a queryable description waiting in the catalog.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
