1

Let says I've this query:

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

which extract all tables of a database, and the number of records on each table.

The question is pretty easy, but can't find how to do it :) How can I order by TableName? Or RowCount?

Putting it on order by clause give me error (i.e. Incorrect syntax near 'SCHEMA_NAME'.).

Thanks for any fancy tips.

0

2 Answers 2

0

After filling the @QueryString you can add your ORDER BY.

DECLARE @QueryString NVARCHAR(MAX);
SELECT
         @QueryString = COALESCE(@QueryString + ' UNION ALL ', '') + N'SELECT '
                        + N'''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + N'.'
                        + QUOTENAME(sOBJ.name) + N''''
                        + N' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                        + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + N'.'
                        + QUOTENAME(sOBJ.name) + N' WITH (NOLOCK) '
FROM     sys.objects AS sOBJ
WHERE    sOBJ.type = 'U'
         AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id),
         sOBJ.name;

SET @QueryString = @QueryString + N' ORDER BY [TableName], [RowCount]';

EXEC sp_executesql @QueryString;
GO
2
  • Works perfect! Thanks
    – markzzz
    Commented Sep 28, 2023 at 7:48
  • Please take note of Charlie's addition. The variable coalescing is not adviced to use.
    – Peter
    Commented Sep 29, 2023 at 7:08
2

As mentioned, you need to just add ORDER BY to the dynamic query.

You shouldn't use variable coalescing to aggregate, it's a documented anti-pattern, and in the presence of things like ORDER BY it often gives incorrect results.

Instead just use STRING_AGG with a WITHIN GROUP parameter.

Note also the join to sys.schemas, and you can use sys.tables rather than filtering sys.objects directly.

DECLARE @QueryString NVARCHAR(MAX);
SELECT @QueryString =
  STRING_AGG(CONVERT(nvarchar(max), '
SELECT
  ''' + REPLACE(QUOTENAME(s.name) + '.' + QUOTENAME(sOBJ.name), '''', '''''') + ''' AS TableName
, COUNT(*) AS RowCount
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK)
'),
    'UNION ALL ') WITHING GROUP (ORDER BY s.name, sOBJ.name)

    + '
ORDER BY TableName, RowCount;'

FROM sys.tables AS sOBJ
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE sOBJ.is_ms_shipped = 0;

EXEC sp_executesql @QueryString;

Note also that if you don't mind getting slightly inaccurate figures, there are much faster ways of getting full table counts.


On older versions of SQL Server, you don't have STRING_AGG so you need the classic hack with FOR XML PATH to get the same thing, and STUFF to knock off the extra separator.

DECLARE @separator nvarchar(100) = 'UNION ALL ';

DECLARE @QueryString NVARCHAR(MAX);
SELECT @QueryString = STUFF((
  SELECT
    @separator + '
SELECT
  ''' + REPLACE(QUOTENAME(s.name) + '.' + QUOTENAME(sOBJ.name), '''', '''''') + ''' AS TableName
, COUNT(*) AS RowCount
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK)
')

    FROM sys.tables AS sOBJ
    JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE sOBJ.is_ms_shipped = 0
    ORDER BY s.name, sOBJ.name
    FOR XML PATH(''), TYPE
).value('text()[1]', 'nvarchar(max)'), 1, LEN(@separator), '') + '
ORDER BY TableName, RowCount;';

EXEC sp_executesql @QueryString;
2
  • It says 'STRING_AGG' is not a recognized built-in function name.
    – markzzz
    Commented Sep 29, 2023 at 7:16
  • Then you are on an old version of SQL Server. You can use FOR XML PATH instead, see edit Commented Sep 29, 2023 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.