Simple-Talk.com came out with a great article titled SQL String User Function Workbench

It has some great sql functions that I’ve spent hours looking for in the past (or had to write myself):

• Split function
• SplitLines
• Within (not from python)
• EndsWith
• StartsWith
• Contains (not from python)
• Join
• Parts (not from python)
• Partition
• RPartition

Anyway, I bookmarked it to go back to in the future.

As far as I could tell there was no easy way to query MSSQL to get a list of all of its tables and their respective sizes. I wrote a sql script that does this for you.

It’s below:

declare @RowCount int, @tablename varchar(100)
declare @Tables table (
PK int IDENTITY(1,1),
tablename varchar(100),
processed bit
)
INSERT into @Tables (tablename)
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc

declare @Space table (
name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100)
)
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = 1
WHILE (@RowCount <> 0)
BEGIN
insert into @Space exec sp_spaceused @tablename
update @Tables set processed = 1 where tablename = @tablename
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = @@RowCount
END

update @Space set data = replace(data, ' KB', '')
update @Space set data = convert(int, data)/1000
update @Space set data = data + ' MB'
update @Space set reserved = replace(reserved, ' KB', '')
update @Space set reserved = convert(int, reserved)/1000
update @Space set reserved = reserved + ' MB'

select * from @Space order by convert(int, replace(data, ' MB', '')) desc

I just ran across this post that was referenced from an article written on MSDN.

For the past 2-3 years I’ve been working under the idea that @@Identity would return the identity of the most recently added row in the table you were just working with. Not so – it will give you the identity of the most recently added row in the entire database! I believe, however, that @@identity is limited to working within the connection to the database. For instance, if you have 2 scripts and they both add a row to the database, they would be using separate connections to the database and thus wouldn’t be confused. If your database uses triggers and your trigger adds a row in another table, @@identity will return the identity of that row, not the one you want.

The alternative offered in the article is to use scope_identity(). It acts the same way, only without this little idiosyncrasy.