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

 

 

Strip unwanted characters out of sql string or variant data

-- =============================================

-- Author:           blowers

-- Create date: 2012-05-11

-- Description:      function to strip out special (escape and other) characters from an input string

-- MODIFY @KEEP TO INCLUDE THE CHARACTERS YOU WISH TO KEEP

--     - in the example a-z, 1-9 and a period will be retained and all others removed

--     - go be the uber-dba and create a list of formats you might want to use (numeric only, alpha only, money formatting only, etc)

-- =============================================

CREATE Function [dbo].[strip_special](@Temp VarChar(1000))

Returns VarChar(1000)

AS

       Begin

              DECLARE @KEEP VARCHAR(50)

              SET @KEEP = '%[^a-z0-9.]%'

              While PatIndex(@KEEP, @Temp) > 0

                     Set @Temp = Stuff(@Temp, PatIndex(@KEEP, @Temp), 1, '')

              Return @TEmp

       End

GO