Object & help system procedures

Status: Reviewed
Stack: PowerShell → SQL / stored procs → Blazor
Depends on: SQL-CLI-09 (overview), SQL-CLI-01 (calling), SQL-CLI-07 (pipeline shapes)
Goal: Know when to use sp_help* / sp_spaceused / sp_helptext / sp_rename vs catalog views/DMVs for inventory and ops scripts feeding Blazor.

Sources: Microsoft Learn (sql-server-ver17 / 2026). Expands SQL-CLI-09 §3.2 (database/objects) only — do not treat this as a rewrite of the overview. Calling: SQL-CLI-01. Staging → Blazor: SQL-CLI-07.

1. Scope map

Write path that stays a proc: sp_rename (and only for the object classes called out below).
Read path for Blazor: always catalog views / DMVs — never scrape multi-result-set sp_help* into a grid.

2. Decision: interactive help procs vs catalog for Blazor staging

Multi-result-set problem (why Blazor hates sp_help*)

Rule for this stack: Collect with a single-select catalog query into dbo.*Staging (CLI-07). Use help procs only for interactive triage or rare control-plane renames.

3. Deep catalog — high-value object / help procs

Columns per proc: purpose · args · result-set quirks · permissions · prefer-instead.

3.1 sp_help

-- Interactive only
EXEC sp_help N'dbo.MyTable';

3.2 sp_helpdb

3.3 sp_helpfile / sp_helpfilegroup

Ops tip: For attach/detach planning, sp_helpfile is a fast interactive peek. For Blazor disk inventory, query sys.master_files once per instance.

3.4 sp_helpindex — and sp_helptable?

sp_helpindex

sp_helptable

Not relevant on modern SQL Server. There is no documented sp_helptable in Learn sql-server-ver17. Sybase heritage / folklore — use sp_help interactively or catalog views for scripts. Do not put it in runbooks.

3.5 ODBC-style catalog procs (brief)

These implement ODBC dictionary functions. Still present; not first-choice for Blazor staging (ODBC column names, wildcards, limited richness).

Worth knowing when debugging ODBC/JDBC metadata; skip for inventory scripts.

3.6 sp_helptext

Encrypted modules caveat

Ops rule: Inventory “do we have the source?” with definition IS NULL on sys.sql_modules joined to sys.objects — not by scraping sp_helptext errors.

3.7 sp_spaceused

@updateusage caveat

• Forces DBCC UPDATEUSAGE — can be expensive on large DBs; takes locks/IO.
• Use only when you suspect wrong space after schema churn (dropped indexes, etc.), not on every inventory tick.
• Prefer scheduled DBCC UPDATEUSAGE in a maintenance window over sprinkling @updateusage='true' in Blazor collectors.

-- Acceptable one-shot from PowerShell (single set)
EXEC sp_spaceused @oneresultset = 1;

-- Prefer for fleets (numeric, joinable) — see §4

3.8 sp_rename

OK vs NEVER

-- Table
EXEC sys.sp_rename N'dbo.OldTable', N'NewTable';           -- @objtype optional for objects
-- Column
EXEC sys.sp_rename N'dbo.T.OldCol', N'NewCol', N'COLUMN';
-- Index
EXEC sys.sp_rename N'dbo.T.IX_Old', N'IX_New', N'INDEX';

-- Modules: do NOT sp_rename — drop/create or publish

3.9 Dependencies — deprecated procs → modern catalog

-- What references this object?
SELECT referencing_schema_name, referencing_entity_name, referencing_class_desc
FROM sys.dm_sql_referencing_entities(N'dbo.MyTable', N'OBJECT');

-- What does this module reference?
SELECT referenced_schema_name, referenced_entity_name, referenced_class_desc,
       is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities(N'dbo.usp_MyProc', N'OBJECT');

-- Database-wide soft dependency edges
SELECT OBJECT_SCHEMA_NAME(referencing_id) AS src_schema,
       OBJECT_NAME(referencing_id) AS src_object,
       referenced_schema_name, referenced_entity_name, referenced_server_name
FROM sys.sql_expression_dependencies;

Limits: No dependency rows for temp tables, rules/defaults (legacy), many dynamic-SQL names, or cross-DB unless recorded. Always treat as necessary but not sufficient before a rename/drop.

3.10 Other object-help procs still worth knowing (thin)

4. Inventory patterns — catalog queries that replace sp_help*

Stable shapes for dbo.*Staging → merge → Blazor (CLI-07). Always SET NOCOUNT ON.

4.1 Objects (replaces bare sp_help)

SET NOCOUNT ON;
SELECT
  @@SERVERNAME AS instance_name,
  DB_NAME() AS database_name,
  SCHEMA_NAME(o.schema_id) AS schema_name,
  o.name AS object_name,
  o.object_id,
  o.type AS type_code,
  o.type_desc,
  o.create_date,
  o.modify_date,
  o.is_ms_shipped,
  SYSUTCDATETIME() AS collected_utc
FROM sys.objects AS o
WHERE o.is_ms_shipped = 0
ORDER BY schema_name, o.type, object_name;

4.2 Columns (replaces sp_help column set / sp_columns)

SET NOCOUNT ON;
SELECT
  SCHEMA_NAME(t.schema_id) AS schema_name,
  t.name AS table_name,
  c.column_id,
  c.name AS column_name,
  ty.name AS type_name,
  c.max_length, c.precision, c.scale,
  c.is_nullable, c.is_identity, c.is_computed,
  c.is_rowguidcol
FROM sys.tables AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
ORDER BY schema_name, table_name, c.column_id;

4.3 Indexes (replaces sp_helpindex)

SET NOCOUNT ON;
SELECT
  SCHEMA_NAME(t.schema_id) AS schema_name,
  t.name AS table_name,
  i.name AS index_name,
  i.index_id, i.type_desc,
  i.is_unique, i.is_primary_key, i.is_unique_constraint,
  i.filter_definition,
  STRING_AGG(
    CASE WHEN ic.is_included_column = 0
         THEN c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
    END, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns
FROM sys.tables AS t
JOIN sys.indexes AS i ON i.object_id = t.object_id AND i.index_id > 0
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
GROUP BY t.schema_id, t.name, i.name, i.index_id, i.type_desc,
         i.is_unique, i.is_primary_key, i.is_unique_constraint, i.filter_definition;

4.4 Module definitions (replaces sp_helptext)

SET NOCOUNT ON;
SELECT
  SCHEMA_NAME(o.schema_id) AS schema_name,
  o.name AS module_name,
  o.type_desc,
  CASE WHEN m.definition IS NULL THEN 1 ELSE 0 END AS is_encrypted_or_unavailable,
  m.definition,          -- nvarchar(max); Invoke-Sqlcmd: raise -MaxCharLength
  m.uses_ansi_nulls, m.uses_quoted_identifier
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE o.is_ms_shipped = 0;

4.5 Space / rows (replaces sp_spaceused fleets)

SET NOCOUNT ON;
SELECT
  SCHEMA_NAME(o.schema_id) AS schema_name,
  o.name AS object_name,
  SUM(ps.row_count) AS row_count,
  SUM(ps.reserved_page_count) * 8 AS reserved_kb,
  SUM(ps.used_page_count) * 8 AS used_kb,
  (SUM(ps.reserved_page_count) - SUM(ps.used_page_count)) * 8 AS unused_kb
FROM sys.dm_db_partition_stats AS ps
JOIN sys.objects AS o ON o.object_id = ps.object_id
WHERE o.type IN ('U', 'V')   -- tables; indexed views appear as V with indexes
  AND ps.index_id IN (0, 1)  -- heap or clustered only for row_count
GROUP BY o.schema_id, o.name
ORDER BY reserved_kb DESC;

4.6 Files / DBs (replaces sp_helpdb / sp_helpfile)

SET NOCOUNT ON;
SELECT
  d.name AS database_name,
  d.database_id, d.state_desc, d.recovery_model_desc,
  d.compatibility_level, d.collation_name,
  SUSER_SNAME(d.owner_sid) AS owner_name,
  mf.name AS logical_name, mf.type_desc,
  mf.physical_name,
  mf.size * 8.0 / 1024 AS size_mb,
  CASE WHEN mf.max_size = -1 THEN NULL ELSE mf.max_size * 8.0 / 1024 END AS max_size_mb,
  mf.growth, mf.is_percent_growth
FROM sys.databases AS d
JOIN sys.master_files AS mf ON mf.database_id = d.database_id
ORDER BY database_name, mf.type, mf.file_id;

5. Gotchas

6. Recipes (sqlcmd + Invoke-Sqlcmd + catalog equivalents)

R1 — Interactive object peek (sqlcmd)

sqlcmd -S "$SQL_SERVER" -E -d "$SQL_DB" -Q "EXEC sp_help N'dbo.MyTable';"

R2 — Indexes interactive (catalog: §4.3)

sqlcmd -S "$SQL_SERVER" -E -d "$SQL_DB" -Q "EXEC sp_helpindex N'dbo.MyTable';"

R3 — sp_spaceused single set (acceptable PS) vs DMV

Invoke-Sqlcmd -ServerInstance $env:SQL_SERVER -Database $env:SQL_DB -TrustServerCertificate `
  -Query "EXEC sp_spaceused @oneresultset = 1;"

# Preferred fleet shape
$q = Get-Content -Raw .\space-by-table.sql   # §4.5
Invoke-Sqlcmd -ServerInstance $env:SQL_SERVER -Database $env:SQL_DB -TrustServerCertificate -Query $q |
  Export-Csv ".\space-$($env:SQL_SERVER)-$($env:SQL_DB).csv" -NoTypeInformation

R4 — Module text: never chunk-scrape

$q = @"
SET NOCOUNT ON;
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, OBJECT_NAME(object_id) AS module_name,
       definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(N'dbo.usp_MyProc');
"@
Invoke-Sqlcmd -ServerInstance $env:SQL_SERVER -Database $env:SQL_DB -TrustServerCertificate `
  -MaxCharLength 1000000 -Query $q

R5 — Encrypted / missing definition audit

SET NOCOUNT ON;
SELECT SCHEMA_NAME(o.schema_id) AS schema_name, o.name, o.type_desc
FROM sys.objects AS o
JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE m.definition IS NULL AND o.is_ms_shipped = 0
ORDER BY 1, 2;

R6 — Rename column (with dependency check first)

-- 1) Who references this table?
SELECT referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities(N'dbo.MyTable', N'OBJECT');

-- 2) Rename
EXEC sys.sp_rename N'dbo.MyTable.OldCol', N'NewCol', N'COLUMN';

-- 3) Refresh any SELECT * modules if needed
-- EXEC sys.sp_refreshsqlmodule N'dbo.vw_Something';

R7 — sqlcmd CSV: object inventory (catalog)

sqlcmd -S "$SQL_SERVER" -E -d "$SQL_DB" -b -s"," -W \
  -Q "SET NOCOUNT ON;
SELECT SCHEMA_NAME(schema_id) AS schema_name, name, type, type_desc, create_date, modify_date
FROM sys.objects WHERE is_ms_shipped = 0 ORDER BY 1, 2;" \
  -o "./objects_${SQL_SERVER}_${SQL_DB}.csv"

R8 — Invoke-Sqlcmd → JSON for Blazor cache / API

$q = @"
SET NOCOUNT ON;
SELECT @@SERVERNAME AS InstanceName, DB_NAME() AS DatabaseName,
  SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS ObjectName,
  o.type AS TypeCode, o.type_desc AS TypeDesc,
  o.create_date AS CreateDate, o.modify_date AS ModifyDate,
  SYSUTCDATETIME() AS CollectedUtc
FROM sys.objects AS o WHERE o.is_ms_shipped = 0;
"@
$rows = Invoke-Sqlcmd -ServerInstance $env:SQL_SERVER -Database $env:SQL_DB `
  -TrustServerCertificate -Query $q
$rows | ConvertTo-Json -Depth 3 | Set-Content ".\objects-$($env:SQL_SERVER).json" -Encoding UTF8

R9 — Dependency gate before drop/rename

sqlcmd -S "$SQL_SERVER" -E -d "$SQL_DB" -b -Q "
SET NOCOUNT ON;
SELECT referencing_schema_name, referencing_entity_name, referencing_class_desc
FROM sys.dm_sql_referencing_entities(N'dbo.MyTable', N'OBJECT');
"

R10 — DB file layout catalog (replaces sp_helpdb/sp_helpfile for staging)

sqlcmd -S "$SQL_SERVER" -E -d master -b -s"," -W -i ./files-inventory.sql -o "./files_${SQL_SERVER}.csv"
# files-inventory.sql body = §4.6

7. Pointers to 09a / 09c / 09d / 09e

Related stack: SQL-CLI-01 (calling / -b / -d), SQL-CLI-07 (Collect→Stage→Blazor), SQL-CLI-03 (-MaxCharLength).

Learn anchors (ver17): sp_help, sp_helpdb, sp_helpfile, sp_helpfilegroup, sp_helpindex, sp_helptext, sp_spaceused, sp_rename, sp_depends (deprecated), Catalog stored procedures, sys.sql_modules, sys.sql_expression_dependencies, sys.dm_sql_referencing_entities, sys.dm_sql_referenced_entities, sys.dm_db_partition_stats.