SQL Server: Schema Discovery Without Admin Access

Everything below runs with ordinary SELECT permission on the target database — no sysadmin, no VIEW SERVER STATE, no ability to click “Include Actual Execution Plan” in SSMS. The sys.* catalog views only ever show metadata for objects you already have some permission on, so they’re safe to run in a locked-down environment. The output of each query is plain, copy-pasteable text — well suited to pasting into an LLM as schema context.

This isn’t just for orientation — it’s the input a good query needs before you write it. Column pruning (SELECT the three columns you need instead of SELECT *) requires knowing which columns exist and which ones are wide (a varbinary(max) or nvarchar(max) you’re pulling across the network for no reason). Row pruning — a WHERE clause that eliminates rows before they come back at all — requires the same thing plus the datatype, since a filter like WHERE CreatedDate >= '2026-01-01' only behaves correctly if you know CreatedDate is a datetime2 and not a varchar holding date-like strings that sort lexically instead of chronologically. The catalog queries below are how you get that shape up front, without a SELECT * against a table whose layout you’re guessing at.

Schemas

SELECT
    s.schema_id,
    s.name AS schema_name,
    dp.name AS owner_name
FROM sys.schemas AS s
JOIN sys.database_principals AS dp ON dp.principal_id = s.principal_id
ORDER BY s.name;

Tables in a schema

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    t.create_date,
    t.modify_date
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE s.name = 'dbo'          -- drop the WHERE to list every schema
ORDER BY s.name, t.name;

Columns and datatypes for a table

sys.columns joined to sys.types gets you the datatype name plus the precision/scale/length info that INFORMATION_SCHEMA.COLUMNS also exposes, but with is_identity and is_computed alongside it:

SELECT
    c.column_id,
    c.name AS column_name,
    ty.name AS data_type,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable,
    c.is_identity,
    c.is_computed
FROM sys.columns AS c
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.YourTableName')
ORDER BY c.column_id;

For nvarchar/varchar/nchar, max_length is in bytes, not characters — halve it for n-prefixed types (or watch for -1, which means MAX).

Same query, but for every table in the database at once — this is the one worth keeping handy, since it’s a single script you can paste straight into an LLM prompt as full schema context:

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    c.column_id,
    c.name AS column_name,
    ty.name AS data_type,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable
FROM sys.columns AS c
JOIN sys.objects AS t ON t.object_id = c.object_id AND t.type = 'U'
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
ORDER BY s.name, t.name, c.column_id;

Finding columns by name across a schema

Useful when you know the shape of what you’re after but not which table it’s in — e.g. hunting down every id-like column (primary keys, foreign keys, natural identifiers) before working out how tables in a schema join together:

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    c.name AS column_name,
    ty.name AS data_type
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
WHERE s.name = 'dbo'
  AND c.name LIKE '%id%'
ORDER BY t.name, c.column_id;

LIKE '%id%' is case-insensitive under the default collation, so it also catches Id, ID, and _Id suffixes — but it’ll match noise too (Valid, Consideration). Tighten it to LIKE '%\_id' ESCAPE '\' or LIKE '%Id' if the naming convention is consistent enough to rely on.

Row counts

COUNT(1) and COUNT(*) are identical in SQL Server — the optimizer rewrites both to a plain row count, so there’s no performance reason to prefer one over the other:

SELECT COUNT(1) AS row_count
FROM dbo.YourTableName;

For a full database, running COUNT(1) per table means a full scan of every table. sys.dm_db_partition_stats gets the same numbers from metadata SQL Server already maintains, no scan required — the difference matters once tables run into the millions of rows:

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(p.row_count) AS row_count
FROM sys.dm_db_partition_stats AS p
JOIN sys.tables AS t ON t.object_id = p.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE p.index_id IN (0, 1)   -- 0 = heap, 1 = clustered index; avoids counting nonclustered indexes separately
GROUP BY s.name, t.name
ORDER BY row_count DESC;

Statistics as a stand-in for the execution plan

Without SHOWPLAN permission, the graphical/XML plan in SSMS just isn’t available — SET STATISTICS XML ON and the “Include Actual Execution Plan” toolbar button both fail with a permissions error. SET STATISTICS IO and SET STATISTICS TIME, though, don’t require SHOWPLAN — they only need permission to run the query itself, since they report on what already happened rather than asking the optimizer to expose its plan:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT *
FROM dbo.YourTableName
WHERE SomeColumn = 'value';

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

The numbers land in the Messages tab, not the results grid:

Table 'YourTableName'. Scan count 1, logical reads 428, physical reads 0, read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 22 ms.

That’s enough to reason about a query’s cost without ever seeing the plan shape itself:

Paste the query text plus the Messages output into an LLM and it has enough to spot the usual culprits — a missing index implied by a high logical-read count on a filtered column, a scan count that suggests a join is driving row-by-row instead of set-based, or a big CPU/elapsed gap worth investigating separately. It’s not the plan itself, but it’s the numbers the plan would have explained.

If SHOWPLAN is granted but the SSMS toolbar option is greyed out or disabled by policy, SET STATISTICS PROFILE ON (legacy, text-based) or SET STATISTICS XML ON (modern, returns an XML plan as a result-set column) both work from plain T-SQL — no GUI feature required, just the permission:

SET STATISTICS PROFILE ON;

SELECT *
FROM dbo.YourTableName
WHERE SomeColumn = 'value';

SET STATISTICS PROFILE OFF;

sys.dm_exec_query_stats — cached plan stats across all sessions, not just the current one — is worth knowing about too, but it needs VIEW SERVER STATE, which is squarely in admin territory and usually isn’t available in the same environments where the tricks above are needed.