USE master
GO
IF OBJECT_ID('sp_GetStruct') IS NULL EXEC ('CREATE PROCEDURE sp_GetStruct AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_GetStruct
@InputSQL VARCHAR(MAX),
@GenerateInsert BIT = 1,
@SelectColumns BIT = 1
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @InputSQL = LTRIM(RTRIM(@InputSQL))
DECLARE @SQL VARCHAR(MAX),
@NewTableName VARCHAR(256),
@Type VARCHAR(10),
@TempTableName VARCHAR(MAX) = '##GetTableDefinition_' + LEFT(REPLACE(CAST(NEWID() AS VARCHAR(500)), '-', ''), 30)
SELECT @Type = Type
FROM sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID(@InputSQL)
IF @Type = 'V'
SET @InputSQL = 'SELECT * FROM ' + @InputSQL
IF @Type = 'U'
BEGIN
SET @NewTableName = Util.dbo.GetTempTableName(@InputSQL)
EXEC sp_script
@ObjectName = @InputSQL,
@CreateTable = 1,
@DropTable = 1,
@UseGOBatchTerminator = 1,
@NewTableName = @NewTableName,
@NewFileGroup = '',
@PrintSQL = 1
EXEC dbo.sp_Insert
@Table = @InputSQL
RETURN
END
ELSE
IF @InputSQL LIKE '#%'
AND OBJECT_ID('tempdb..' + @InputSQL) IS NOT NULL
BEGIN
SELECT @NewTableName = ISNULL((SELECT Util.dbo.GetTempTableName(name)
FROM tempdb.sys.objects (NOLOCK)
WHERE OBJECT_ID = OBJECT_ID('tempdb..' + @InputSQL)), @InputSQL)
EXEC('USE tempdb
EXEC sp_script
@ObjectName = ''' + @InputSQL+ ''',
@CreateTable = 1,
@NewFileGroup = '''',
@NewTableName = ''' + @NewTableName + ''',
@UseGOBatchTerminator = 1,
@DropTable = 1,
@PrintSQL = 1
EXEC dbo.sp_Insert @Table = ''TEMPDB..' + @InputSQL + '''
SELECT c.name AS ColumnName,
ct.ColumnType AS ColumnDef,
c.column_id AS ColumnId,
ic.key_ordinal AS PKOrdinal,
c.is_nullable AS IsNullable,
c.is_identity AS IsIdentity,
c.max_length AS MaxLength,
c.is_computed AS IsComputed,
dc.definition AS DefaultDefinition,
ck.definition AS CheckDefinition,
cc.DEFINITION AS ComputeDefinition
FROM tempdb.sys.schemas s (NOLOCK)
INNER JOIN tempdb.sys.objects o (NOLOCK) ON s.schema_id = o.schema_id
INNER JOIN tempdb.sys.columns c (NOLOCK) ON c.object_id = o.object_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN tempdb.sys.types y (NOLOCK) ON y.user_type_id = c.user_type_id
INNER JOIN tempdb.sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
LEFT OUTER JOIN tempdb.sys.indexes i (NOLOCK) ON i.object_id = o.object_id
AND i.is_primary_key = 1
LEFT OUTER JOIN tempdb.sys.index_columns ic (NOLOCK) ON ic.object_id = o.object_id
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
LEFT OUTER JOIN tempdb.sys.default_constraints dc (NOLOCK) ON dc.parent_object_id = o.object_id
AND dc.parent_column_id = c.column_id
LEFT OUTER JOIN tempdb.sys.check_constraints ck (NOLOCK) ON ck.parent_object_id = o.object_id
AND ck.parent_column_id = c.column_id
LEFT OUTER JOIN tempdb.sys.computed_columns cc (NOLOCK) ON cc.object_id = o.object_id
AND cc.column_id = c.column_id
CROSS APPLY Util.dbo.GetColumnType(y.name, c.max_length, c.precision, c.scale, c.collation_name, db.collation_name) ct
WHERE o.OBJECT_ID = object_id(''TEMPDB..' + @InputSQL + ''')
')
RETURN
END
ELSE
IF @InputSQL LIKE '%#%'
BEGIN
SELECT @NewTableName = 'TableDef',
@SQL = ';WITH aa as(' + @InputSQL + ')
SELECT * INTO ' + @TempTableName + ' FROM aa WHERE 1 = 0'
EXEC(@SQL)
END
ELSE
BEGIN
SELECT @NewTableName = 'TableDef',
@SQL = 'SELECT a.* INTO ' + @TempTableName + ' FROM (' + @InputSQL + ') a WHERE 1 = 0'
BEGIN TRY
EXEC(@SQL)
END TRY
BEGIN CATCH
SET @SQL = 'SELECT a.* INTO ' + @TempTableName + ' FROM OPENROWSET(''SQLNCLI'', ''Server=' + @@SERVERNAME + ';DATABASE=' + DB_NAME()
+ ';Trusted_Connection=yes;'', ''' + 'SET FMTONLY ON
' + REPLACE(@InputSQL, '''', '''''') + '
'') AS a;'
EXEC(@SQL)
END CATCH
END
SET @SQL = 'USE tempdb
EXEC sp_script
@ObjectName = ''' + @TempTableName + ''',
@DropTable = 1,
@CreateTable = 1,
@UseGOBatchTerminator = 1,
@NewTableName = ''' + @NewTableName + ''',
@NewFileGroup = '''',
@PrintSQL = 1
' + CASE WHEN @GenerateInsert = 1 THEN 'EXEC dbo.sp_Insert @Table = ''' + @TempTableName + ''', @NewTableName = ''' + @NewTableName + ''''
ELSE ''
END + '
SELECT c.name AS ColumnName,
ct.ColumnType AS ColumnDef,
c.column_id AS ColumnId,
ic.key_ordinal AS PKOrdinal,
c.is_nullable AS IsNullable,
c.is_identity AS IsIdentity,
c.max_length AS MaxLength,
c.is_computed AS IsComputed,
dc.definition AS DefaultDefinition,
ck.definition AS CheckDefinition,
cc.DEFINITION AS ComputeDefinition
FROM tempdb.sys.schemas s (NOLOCK)
INNER JOIN tempdb.sys.objects o (NOLOCK) ON s.schema_id = o.schema_id
INNER JOIN tempdb.sys.columns c (NOLOCK) ON c.object_id = o.object_id
LEFT OUTER JOIN Util.dbo.SystemObjectTypes ot ON ot.Type = o.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN tempdb.sys.types y (NOLOCK) ON y.user_type_id = c.user_type_id
INNER JOIN tempdb.sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
LEFT OUTER JOIN tempdb.sys.indexes i (NOLOCK) ON i.object_id = o.object_id
AND i.is_primary_key = 1
LEFT OUTER JOIN tempdb.sys.index_columns ic (NOLOCK) ON ic.object_id = o.object_id
AND ic.index_id = i.index_id
AND ic.column_id = c.column_id
LEFT OUTER JOIN tempdb.sys.default_constraints dc (NOLOCK) ON dc.parent_object_id = o.object_id
AND dc.parent_column_id = c.column_id
LEFT OUTER JOIN tempdb.sys.check_constraints ck (NOLOCK) ON ck.parent_object_id = o.object_id
AND ck.parent_column_id = c.column_id
LEFT OUTER JOIN tempdb.sys.computed_columns cc (NOLOCK) ON cc.object_id = o.object_id
AND cc.column_id = c.column_id
CROSS APPLY Util.dbo.GetColumnType(y.name, c.max_length, c.precision, c.scale, c.collation_name, db.collation_name) ct
WHERE o.OBJECT_ID = object_id(''' + @TempTableName + ''')
DROP TABLE ' + @TempTableName
EXEC(@SQL)
GO
EXEC sys.sp_ms_marksystemobject sp_GetStruct
GO