When generating table specification directly using the T-SQL query by querying system views you provided an error occurs when there are tables with many columns. Error:
"STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation."
The solution, which I found here: https://andyhogg.wordpress.com/2019/01/09/a-couple-of-notes-on-the-string_agg-function/
, is to cast to NVARCHAR(MAX).
the modified T-SQL query is:
select CONCAT('new TableSpec("',schema_name(t.schema_id), '","', t.name, '")') + string_agg(CONCAT('.AddColumn("', cast(c.name as NVARCHAR(MAX)), '", "', cast(tp.name as NVARCHAR(MAX)), '", isKeyColumn:', IIF(ix.is_primary_key = 1, 'true', 'false'), '))'),'') from sys.tables t join sys.columns c on t.object_id = c.object_id join sys.types tp on c.system_type_id = tp.system_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 ix on ic.index_id = ix.index_id and ic.object_id = ix.object_id --where t.name in ('','','') --> specify target tables if needed. group by t.schema_id, t.name
When generating table specification directly using the T-SQL query by querying system views you provided an error occurs when there are tables with many columns. Error:
"STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation."
The solution, which I found here: https://andyhogg.wordpress.com/2019/01/09/a-couple-of-notes-on-the-string_agg-function/
, is to cast to NVARCHAR(MAX).
the modified T-SQL query is:
select CONCAT('new TableSpec("',schema_name(t.schema_id), '","', t.name, '")') + string_agg(CONCAT('.AddColumn("', cast(c.name as NVARCHAR(MAX)), '", "', cast(tp.name as NVARCHAR(MAX)), '", isKeyColumn:', IIF(ix.is_primary_key = 1, 'true', 'false'), '))'),'') from sys.tables t join sys.columns c on t.object_id = c.object_id join sys.types tp on c.system_type_id = tp.system_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 ix on ic.index_id = ix.index_id and ic.object_id = ix.object_id --where t.name in ('','','') --> specify target tables if needed. group by t.schema_id, t.name