SQL Server Capacity Constraints – Dynamic Retrieval Methods

information-schemasql server

I have some code which creates database tables dynamically, and I want to ensure that people don't try to do things that result in me trying to create a table with more columns than what SQL Server allows.

According to this article, the limit for the max number of columns per (nonwide) table is 1024.

However, this can obviously change in the future, so I don't want to hardcode it in my app.

Is there a way to get this value dynamically using a SQL query (e.g. by reading from some system table or proc)?

Best Answer

No there's nothing built in that is exposed to us.

Your app would need to maintain some sort of configuration table itself.

You can't even try and parse it out of sys.messages as it isn't hardcoded into the text for message_id=1702.

CREATE TABLE failed because column '%.*ls' in table '%.*ls' exceeds the maximum of %d columns.

Your app could of course try and dynamically discover the limit by simply trying CREATE TABLE statements with ever greater amounts of columns but that is not a serious suggestion.

Related Solutions

Sql-server – SQL Server 2008 R2 DDL Trigger to create DML Triggers on all new user tables

From SQL 2005 onwards I would use sys.tables which is scoped to user tables and includes the is_ms_shipped column, something like:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE 
    FOR CREATE_TABLE 
AS 
BEGIN
    SET NOCOUNT ON
    SET ANSI_PADDING ON
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @table_name SYSNAME
    DECLARE @xml XML

    SET @xml = EVENTDATA() 

    SELECT 
        @table_name = QUOTENAME( t.c.value('(SchemaName/text())[1]', 'SYSNAME') ) + '.' + 
                      QUOTENAME( t.c.value('(ObjectName/text())[1]', 'SYSNAME') )
    FROM @xml.nodes( 'EVENT_INSTANCE') t(c)


    IF EXISTS ( SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(@TABLE_NAME) AND is_ms_shipped = 0 )
    BEGIN
        RAISERROR( 'Creating replication trigger for table %s.', 10, 1, @TABLE_NAME )
        EXEC sp_CreateReplTrigger @TABLE_NAME
    END
    ELSE
        -- Raise low-level error which won't cause trigger to fail, but will print message to indicate which table has been ignored
        RAISERROR( 'Table %s ignored for replication trigger proc.', 10, 1, @TABLE_NAME )
END

In a quick test on some of my servers, this excluded MS* and sys* tables including MSreplication_subscriptions etc

SQL Server – Replicating Wide Table in MS SQL

To make a table into a wide table, you must create sparse columns and also add a column set. If you don't create a column set your table will still be limited to 1024 columns. This is why you can't use replication for wide tables.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 R2 DDL Trigger to create DML Triggers on all new user tables

SQL Server – Replicating Wide Table in MS SQL

Related Question