How to get data types of columns when a CREATE TABLE FROM SELECT is forbidden?-CodePudding

I've finished a big query in SSMS with a lot of columns. The result will be inserted in another server.

Usually, I create a temporary table to quickly get the data types of each columns (CREATE TABLE from a SELECT).

But now, I'm working on a server where it is forbidden to create a table (even a temporary one).

How can I get the structure of my query easily, without re-typing each column manually?

EDIT 1 : I query on an Azure SQL Server Database. And my result will go to an On Premise SQL Server.

EDIT 2 : SQL Server 2016

CodePudding user response：

If you have a query that you can construct as dynamic SQL (or in an object), you can usually inspect the types involved using sys.dm_exec_describe_first_result_set:

DECLARE @sql nvarchar(max) = N'SELECT database_id, name FROM sys.databases;';

SELECT name, 
       system_type_name, 
       is_nullable
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0) AS f
ORDER BY f.column_ordinal;

Results (db<>fiddle):

name	system_type_name	is_nullable
database_id	int	False
name	nvarchar(128)	True

So you could use that output to build a CREATE TABLE statement, e.g.:

DECLARE @sql    nvarchar(max), 
        @create nvarchar(max) = N'CREATE TABLE dbo.DBs
(--';
 
SET @sql = N'SELECT database_id, name FROM sys.databases;';
 
SELECT @create  = ','   CHAR(13)   CHAR(10)   '  '
                 name   ' ' 
                 system_type_name 
                 CASE is_nullable WHEN 0 THEN ' NOT' ELSE ' ' END
                 ' NULL'
  FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0) AS f;
 
SELECT @create  = CHAR(13)   CHAR(10)   N');';
 
PRINT @create;
-- EXEC sys.sp_executesql @create;

Output (db<>fiddle):

CREATE TABLE dbo.DBs
(--,
  database_id int NOT NULL,
  name nvarchar(128)  NULL
);

Some background: