Table Schema Viewer

Overview

The Table Schema Viewer is a TSQL.APP Action that displays table structure information in a modal window. It works on any Card and shows column details, data types, and key information.

Code

--	AI assistant at: https://tsql.app
--  table structure information
--  this action code displays table structure information of the basetable of the current card

-- Step 1: Check if @TableName exists
IF @TableName IS NULL
BEGIN
    EXEC sp_api_modal_text @text=N'Error: Table name is not defined in @TableName.', @class='text-danger';
    RETURN;
END

-- Step 2: Find the base_object_name of the synonym
DECLARE @BaseObject NVARCHAR(255);

SELECT 
    @BaseObject = base_object_name
FROM 
    sys.synonyms
WHERE 
    name = @TableName;

-- Check if base_object_name was found
IF @BaseObject IS NULL
BEGIN
    EXEC sp_api_modal_text @text=N'Error: Synonym does not resolve to a valid base object.', @class='text-danger';
    RETURN;
END

-- Step 3: Prepare dynamic SQL
DECLARE @DynamicSQL NVARCHAR(MAX);

SET @DynamicSQL = N'
USE ' + QUOTENAME(PARSENAME(@BaseObject, 3)) + N'; -- Switch to the correct database
SELECT 
    c.name AS [Column Name],
    t.name AS [Data Type],
    CASE c.is_nullable WHEN 1 THEN ''Yes'' ELSE ''No'' END AS [Is Nullable],
    c.max_length AS [Max Length],
    CASE WHEN i.is_primary_key = 1 THEN ''Yes'' ELSE ''No'' END AS [Primary Key]
FROM 
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN 
    sys.index_columns ic ON c.column_id = ic.column_id AND c.object_id = ic.object_id
LEFT JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE 
    c.object_id = OBJECT_ID(''' + PARSENAME(@BaseObject, 2) + N'.' + PARSENAME(@BaseObject, 1) + N''')';

-- Step 4: Create a temporary table for the results
IF OBJECT_ID('tempdb..#TableSchema') IS NOT NULL DROP TABLE #TableSchema;
CREATE TABLE #TableSchema (
    [Column Name] NVARCHAR(128),
    [Data Type] NVARCHAR(128),
    [Is Nullable] NVARCHAR(3),
    [Max Length] INT,
    [Primary Key] NVARCHAR(3)
);

-- Step 5: Execute dynamic SQL and populate the temporary table
INSERT INTO #TableSchema
EXEC sp_executesql @DynamicSQL;

-- Step 6: Display the results in a modal
DECLARE @ModalTitle NVARCHAR(MAX) = CONCAT(N'Schema for Table: ', @TableName);
EXEC sp_api_modal_text @text=@ModalTitle, @class='h3';

EXEC sp_api_modal_table 
    @tmptable='#TableSchema', 
    @print=1, 
    @excel=1, 
    @orderby='order by [Column Name]';

-- Step 7: Add a close button
DECLARE @CloseButton NVARCHAR(MAX);
EXEC sp_api_modal_button 
    @name='@CloseButton', 
    @value='Close', 
    @valueout=@CloseButton OUT, 
    @class='btn-secondary';

-- Step 8: Clean up the modal when closing
IF @CloseButton IS NOT NULL
BEGIN
    EXEC sp_api_modal_clear;
END

Code Explanation

Input Validation

IF @TableName IS NULL
BEGIN
    EXEC sp_api_modal_text @text=N'Error: Table name is not defined in @TableName.'
    RETURN;
END

Checks if the context variable @TableName exists.

Synonym Resolution

SELECT @BaseObject = base_object_name
FROM sys.synonyms
WHERE name = @TableName;

Gets the actual table name if @TableName is a synonym. Important for linked server scenarios.

Dynamic SQL Generation

SET @DynamicSQL = N'
SELECT 
    c.name AS [Column Name],
    t.name AS [Data Type]...';

Builds query to fetch metadata from system views. Uses PARSENAME to handle three-part names.

Temporary Storage

CREATE TABLE #TableSchema (...);
INSERT INTO #TableSchema
EXEC sp_executesql @DynamicSQL;

Creates temp table for results, allowing sorting and Excel export.

UI Components

  • Modal title: sp_api_modal_text
  • Results table: sp_api_modal_table
  • Close button: sp_api_modal_button
  • Cleanup: sp_api_modal_clear

Framework Integration

Context Variables

  • @TableName: Provided by TSQL.APP
  • Contains current Card's table name

Modal API

  • Text display: sp_api_modal_text
  • Table display: sp_api_modal_table
  • Button creation: sp_api_modal_button
  • Modal cleanup: sp_api_modal_clear

Implementation Guide

Adding the Action

  1. Open Card settings
  2. Create new Action
  3. Paste code
  4. Save

Using the Action

  1. Click Action button
  2. View schema in modal
  3. Export if needed
  4. Close when done

Error Handling

  • Missing table name
  • Invalid synonym
  • Permission issues
  • Database context

Use Cases

Development

  • Check data types
  • Verify constraints
  • Plan queries

Documentation

  • Export schemas
  • Create data dictionaries
  • Share structure info

Troubleshooting

  • Verify field lengths
  • Check nullability
  • Confirm keys

Best Practices

  1. Add to frequently used Cards
  2. Export results for documentation
  3. Check permissions beforehand
  4. Use for data validation planning

Tips

  • Sort by clicking column headers
  • Use Excel export for sharing
  • Check synonym resolution
  • Reference for query writing