How nice would be if you could design fancy-schmancy stuff in excel and plug that into your SQL process engine. Just image the possibilities, all those nasty algorithms you could come up with would be easily available to end users. Basically you would design what you wanted using a sample dataset in Excel with the same columns definitions as your real dataset. Somehow the you could drive your resultset through the excel engine and use the output. If you wanted to get fancy for larger datasets you could implement a mechanism where data could get streamed in chunks just large enough to surface the required calcs in Excel. E.g you are doing some time based calc that requires a full weeks worth of data to operate, you could then stream years worth of data in week chunks to the Excel engine. I bet this would beat the pants off of most complicated native sql based solutions as unless your well versed in sql and have a decent model within your control things start getting very slow very quickly for the average sql developer.
Recently found myself needing something along the lines of Oracle’s describe utility. The good news is SQL Server has something along those lines namely
sp_columns tablename will list the tables column metadata
sp_help tablename will list the tables extended metadata including column some metadata
Definitely comes in handy
declare @sqlstr nvarchar(1000) declare @filter nvarchar(50) select @filter = '%DTS%' create table #tbldbobjects ( dbname sysname, objname varchar(200), objtype char(2), objuserid int ) select @sqlstr = 'sp_msforeachdb ''IF DB_ID(''''?'''') > 4 Insert into #tblDBObjects select ''''?'''' as DBName, name, xtype, uid From ?..sysobjects where name like ''''' + @filter + '''''''' exec sp_executesql @sqlstr select * from #tbldbobjects order by dbname, objname drop table #tbldbobjects