none
Printing select results with multiple rows returned RRS feed

  • Question

  • I need to generate reports of databases and their tables using print output for a report.  For each database, I need to query for the list of tables, then for each row, print it out.  The result should look like something similar to this:

    Database Name: Name of Database
    Tables:
    Name of Table

    I'm not sure how to accomplish this.  So far I've got a cursor opened because I'll have to print more things per database, like the list of users, schemas, roles, all that stuff.  But if I get one example of how to print the query results then that would solve it for me.  So far this is my general t-sql which isn't work on the print output, the printing of the database name is just fine, it's the line to print the list of tables which is failing.

                 

    DECLARE database_cursor CURSOR FOR   
    SELECT name from sys.databases;  

    OPEN database_cursor  

    FETCH NEXT FROM database_cursor   
    INTO @databasename  

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        PRINT ' '  
        SELECT @message = '----- Database: ' +  @databasename  

        PRINT @message  

        set @cmd='use ' + @databasename + '; DECLARE @results nvarchar; SELECT @results=(SELECT * FROM sys.tables); PRINT @results'

    exec sp_executesql @cmd

    FETCH NEXT FROM database_cursor INTO @databasename

    END   
    CLOSE database_cursor;  
    DEALLOCATE database_cursor;  

    Thursday, December 19, 2019 5:52 PM

All replies

  • how are you doing the report ? ssrs or something else.

    you can get list of all tables with a direct query and then you use report to format the layout.

    select * from information_schema.tables should list all tables in a db.


    Hope it Helps!!

    Thursday, December 19, 2019 6:33 PM
  • Try this too:

    set @cmd = 
         'USE ' + QUOTENAME(@databasename) + '
          DECLARE @results nvarchar(MAX) = ''''
    
          SELECT @results += [name] + CHAR(13) + CHAR(10) 
          FROM sys.tables 
          ORDER BY [name] 
    
          PRINT @results '
    


    Thursday, December 19, 2019 7:28 PM
  • Hi , 

    Or please try to use following script .

    set @cmd='use ' + @databasename + ';
    IF OBJECT_ID(''result'') IS NOT NULL drop table  result   
    select * into result FROM sys.tables 
    select * from result
    '

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 20, 2019 5:28 AM
  • T-SQL is not a report language. Produce a relational result set from the database, and then use a report-writing tool to produce the report. There is a PRINT command, but it is intended for diagnostics and debugging.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 20, 2019 8:08 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 25, 2019 6:21 AM