Created
February 8, 2023 19:55
-
-
Save kevinmartintech/be8340da4866824c3018faecb37e2662 to your computer and use it in GitHub Desktop.
Automatically create a table variable (or temp table) in which to store the results of (for example) executing a stored procedure, function or query
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
| /** | |
| Summary: > | |
| This is a temporary batch for creating such things as table variables, | |
| temporary tables or anything else that needs a column list | |
| Author: Phil Factor | |
| Based on code from https://www.red-gate.com/hub/product-learning/sql-prompt/building-reusable-table-build-scripts-using-sql-prompt | |
| **/ | |
| Declare @TheExpression NVARCHAR(MAX)= | |
| N'$SELECTEDTEXT$' | |
| SELECT 'DECLARE @$TableVariable$ table ('+ | |
| Stuff ((SELECT ', | |
| '+QuoteName(Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name)) | |
| + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL' | |
| --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END | |
| AS ThePath | |
| FROM sys.dm_exec_describe_first_result_set | |
| (@TheExpression, NULL, 1) AS f | |
| -- use sys.sp_describe_first_result_set for a batch | |
| LEFT OUTER JOIN | |
| (SELECT name AS name FROM sys.dm_exec_describe_first_result_set | |
| (@TheExpression, NULL, 0) WHERE is_hidden=0 | |
| GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames | |
| ON DetectDuplicateNames.name=f.name | |
| WHERE f.is_hidden=0 | |
| ORDER BY column_ordinal | |
| FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment