Table Schema Viewer

While building an application you constantly need to know what is in a table: column names, types, which column is the key. This action answers that for whatever card you are looking at. Make it a global action and it is available on every screen.

The script

DECLARE @Probe       NVARCHAR(MAX);
DECLARE @Title       NVARCHAR(MAX);
DECLARE @CloseButton NVARCHAR(MAX);

EXEC sp_api_modal_get_value @name = N'@CloseButton', @value = @CloseButton OUT;
IF @CloseButton IS NOT NULL
BEGIN
    EXEC sp_api_modal_clear;
    RETURN;
END

IF @tablename IS NULL
BEGIN
    EXEC sp_api_modal_alert @alert_text = N'Open a card first.';
    RETURN;
END

SET @Probe = CONCAT(N'SELECT * FROM ', QUOTENAME(@tablename));

SELECT [#]        = column_ordinal,
       [Column]   = name,
       [Type]     = system_type_name,
       [Nullable] = IIF(is_nullable = 1, N'yes', N''),
       [Key]      = IIF(name = @identity_column, N'key', N''),
       [Identity] = IIF(is_identity_column = 1, N'yes', N''),
       [Computed] = IIF(is_computed_column = 1, N'yes', N'')
INTO #Schema
FROM sys.dm_exec_describe_first_result_set(@Probe, NULL, 0);

SET @Title = CONCAT(N'Columns of ', @tablename, N' (card: ', @card_name, N')');
EXEC sp_api_modal_text  @text = @Title, @class = N'h4';
EXEC sp_api_modal_table @tmptable = N'#Schema', @orderby = N'ORDER BY [#]', @excel = 1;
EXEC sp_api_modal_button @name = N'@CloseButton', @value = N'Close',
     @valueout = @CloseButton OUT, @class = N'btn-outline-secondary';

How it works

The card tells you what to describe. @tablename is a context variable: the table or view behind the current card, set by the framework before the script starts. @card_name and @identity_column come with it. None of them is declared in the script, and none should be.

SQL Server describes the table for you. sys.dm_exec_describe_first_result_set takes a query and returns the columns it would produce — names, types, nullability, identity and computed flags — without running it. Asking it about SELECT * FROM <card table> is the cheapest possible way to describe a table, and it works on views as well as tables.

It works through the application's connection to the business database. A card's table lives in the business database, but actions run in the application's metadata database, where the table is reachable under its plain name. That is why the probe needs no database prefix, no USE and no lookup of where the table really lives.

QUOTENAME keeps the probe safe. The table name goes into a query string, so it is quoted — a habit for any name you put into SQL text, even one that comes from metadata.

Making it global

An action becomes available on every card when it is marked global. See the global flag in the Programmer's Manual. Limit it to developer or administrator roles: it is a tool for building, not for end users.