Script Table or View structure
My 2010 thankyou to the Forum
I have noticed a few enquiries this year from users who wanted to script table/view structures. Normal procedure is to tell them to use Management Studio or sp_help. Thought about it a bit and figured that the requirement is really to dynamically generate the structure so that it may be manipulated. As such this is my contribution for 2010.
Please feel free to use it however you like. Just give credit where credits due.
Usage as always is at the top of the script in the remarks
CREATE proc dbo.xsp_Structure (@TabularObject varchar(max))
as begin
/*
Name : xsp_Structure
Description : Passed in the name of a view or table it returns
its structure.
Developed By: Pratap J Prabhu
Usage/Examples:
---------------
1. exec xsp_Structure 'myTableName'
2. set nocount on
declare @tbl table (ColName varchar(50)
,ColType varchar(50)
,ColLen int
,ColDeci int
)
insert into @Tbl (ColName,ColType,ColLen,ColDeci) exec xsp_Structure 'myTableOrViewName'
declare @Col varchar(50)
,@Type varchar(50)
,@Len int
,@Deci int
declare myCurs Cursor for select * from @tbl
open myCurs
fetch next from myCurs into @Col,@Type,@Len,@Deci
while @@FETCH_STATUS=0
begin
print @Col ----- do what you want here
fetch next from myCurs into @Col,@Type,@Len,@Deci
end
close myCurs
deallocate myCurs
*/ set nocount on
declare @sql nvarchar(255)
set @sql='select top 1 * into ##myStruct from ' + @TabularObject
exec sp_executesql @sql
select ColName=[Name]
,ColType = type_name(user_type_id)
,ColLen= convert(int, case when user_type_id in (52,56,60,62,106) then precision else max_length end)
,ColDeci= case when user_type_id in (52,56,60,62,106) then scale else 0 end
from tempdb.sys.columns where object_id = object_id('tempdb..##myStruct');
drop table ##myStruct
end
go 
