Tag Archives: SQL

SQL 22005 / 2008

Compiling CLR without Visual Studio

To Compile a VB or C# module as a CLR routine.

In a dos prompt
(c#)

path=%path%;c:windowsmicrosoft.netframeworkv3.5
csc  /out:UserDefinedFunctions.dll /target:library userdefinedsourcefile.cs

(vb)
path=%path%;c:windowsmicrosoft.netframeworkv3.5
vbc  /out:UserDefinedFunctions.dll /target:library userdefinedsourcefile.vb

Not that this doesn’t give you the benefits of tracing that you get using visual studio but there are cases where a clr routine is faster to write (I’m thinking string manipulation here) than the equivalent inline code

SQL 2000

Getting space used from tables – in one command

2000 only getting space used from tables

This isn’t a new tip – you may have seen many references to using sp_MSForEachTable and it’s partner sp_MSForEachDB – just put it into context with getting the data back out in a clean format by using insert .. exec


/* create a table to hold the results of the exec command */
create table #SpaceUsed (name nvarchar(129), rows char(11), reserved varchar(18),data varchar(18), index_Size varchar(18), unused varchar(18))
insert #SpaceUsed exec sp_msforeachTable 'sp_spaceused "?"'
--Run the script - then
select * from #SpaceUsed order by cast(rows as int) desc
--gives you the number of rows
select * from #SpaceUsed order by cast(left(reserved,len(reserved) - 3)as int) desc

–Gives you the data based on space used (including indexes)

Note that in SQL 2008 and subsequent versions – you can simply use the standard reports in the database e.g. right click on the database -> Reports ->Standard Reports->Disk Usage by Tables which can then be exported e.g. in excel by right clicking on the report -> Export -> Excel

I haven’t added that in as a SQL 2008 tip since most people already know about the custom reports – however, worth bearing in mind that compatability must be set to at least 90 for the custom reports to work!