Script to retrieve temp table definition

So you created a query using a select into to create a temp table.....

And now you you need to productionalize it.  First order; get rid of the select into that is tying up your TempDB.

But how to go back and find out the size and datatype of all those columns you just stuffed into the temp table.  A select into requires none of that, right?  Going to each of the individual tables and getting the definitions can be time consuming.  Except....


USE TEMPDB;

SELECT

    c.COLUMN_NAME

    ,c.DATA_TYPE

    ,c.CHARACTER_MAXIMUM_LENGTH

    ,c.NUMERIC_PRECISION

    ,c.NUMERIC_SCALE

FROM INFORMATION_SCHEMA.COLUMNS c

join INFORMATION_SCHEMA.TABLES t

    on c.TABLE_NAME = t.TABLE_NAME

where

    c.TABLE_NAME like '#your_table_name%'

ORDER BY

    c.TABLE_NAME

    ,c.ORDINAL_POSITION