As much as I try to disuade people from building processes that might come back to haunt them later, this one always seems to come up. So I gave in, I figure it might be useful from an admin perspective at some point, so here it is.
Use it at you're own risk, and don't go swimming at least 1 hour after use.
CREATE PROC usp_CopyTable
@TABLE_CATALOG sysname
, @TABLE_SCHEMA sysname
, @TABLE_NAME sysname
, @TMP_TABLE_NAME sysname OUT
AS
SET NOCOUNT ON
BEGIN
DECLARE @sql varchar(8000), @GetDate varchar(25)
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_CATALOG = @TABLE_CATALOG
AND TABLE_SCHEMA = @TABLE_SCHEMA
AND TABLE_NAME = @TABLE_NAME)
BEGIN
SET @GetDate = REPLACE(REPLACE(REPLACE(CONVERT(varchar(25),GetDate(),120),'-','_'),' ','_'),':','_')
SET @TMP_TABLE_NAME = '['+@TABLE_CATALOG+'].['+@TABLE_SCHEMA+'].['+'TMP_'+@TABLE_NAME+'_'+@GetDate+']'
SET @sql = 'SELECT * INTO '+@TMP_TABLE_NAME+' FROM '
+'['+@TABLE_CATALOG+'].['+@TABLE_SCHEMA+'].['+@TABLE_NAME+']'
EXEC(@sql)
END
ELSE
PRINT 'Table ' + '['+@TABLE_CATALOG+'].['+@TABLE_SCHEMA+'].['+@TABLE_NAME + '] Does not Exists'
RETURN
SET NOCOUNT OFF
END
GO
DECLARE @TMP_TABLE_NAME sysname, @sql varchar(8000)
EXEC usp_CopyTable 'Northwind','dbo','Order Details',@TMP_TABLE_NAME OUT
SELECT @TMP_TABLE_NAME
SELECT @sql = 'SELECT COUNT(*) FROM ' + @TMP_TABLE_NAME
EXEC(@sql)
SELECT @sql = 'DROP TABLE ' + @TMP_TABLE_NAME
GO
DROP PROC usp_CopyTable
GO