Technical Article

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

Rate

1 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (4)

You rated this post out of 5. Change rating