Created
October 20, 2024 12:07
-
-
Save tareq2/c45f9a10b6d9eeb60b2065bb1803dfeb to your computer and use it in GitHub Desktop.
Make select for all tables in sql server and split the output without truncating the output.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --make sure to increase the text output from options | |
| DECLARE @sql NVARCHAR(MAX) = ''; | |
| -- Build the dynamic SQL query | |
| SELECT @sql = @sql + | |
| 'SELECT TOP 0 ' + STUFF((SELECT ', ' + QUOTENAME(COLUMN_NAME) | |
| FROM INFORMATION_SCHEMA.COLUMNS AS c | |
| WHERE c.TABLE_NAME = t.TABLE_NAME | |
| FOR XML PATH('')), 1, 2, '') + | |
| ' FROM ' + QUOTENAME(t.TABLE_NAME) + ';' + CHAR(13) | |
| FROM INFORMATION_SCHEMA.TABLES AS t | |
| WHERE t.TABLE_TYPE = 'BASE TABLE'; | |
| -- Declare a variable for chunking the string | |
| DECLARE @printString NVARCHAR(4000); | |
| DECLARE @spaceIndex INT; | |
| -- Loop to print chunks of 4000 characters, split at spaces | |
| WHILE LEN(@sql) > 0 | |
| BEGIN | |
| -- Find the last space within the first 4000 characters | |
| SET @spaceIndex = CHARINDEX(' ', REVERSE(LEFT(@sql, 4000))); | |
| -- If there's a space, split at that point; otherwise, split at 4000 characters | |
| IF @spaceIndex > 0 | |
| BEGIN | |
| SET @printString = LEFT(@sql, 4000 - @spaceIndex); | |
| PRINT @printString; | |
| SET @sql = RIGHT(@sql, LEN(@sql) - LEN(@printString)); | |
| END | |
| ELSE | |
| BEGIN | |
| SET @printString = LEFT(@sql, 4000); | |
| PRINT @printString; | |
| SET @sql = RIGHT(@sql, LEN(@sql) - 4000); | |
| END | |
| END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment