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):
Anyway, I bookmarked it to go back to in the future.
MSSQL Table Size for all tables
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
@@Identity Best Practices (MS SQL)
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.
This may not interest many of you, but I found a great article on designing database tables in Microsoft SQL Server 2000. Before reading, I had a general understanding of all the types, but I didn’t have any base for choosing which type to use where.
You may want to check it out:
http://www.mssqlcity.com/Articles/General/choose_data_type.htm
SQL Prompt is a great program put out by Red Gate.
They just released a deal where they are giving it away for free until September 1st 2006 (That’s when they will release a new version of the product.)
I downloaded it and tried it out, and it works great - very handy to have when you are dealing with tables with lots of fields, etc. It works with Microsoft Query Analyzer, SQL Server 2005 Management Studio, Visual Studio 2005, Visual Studio .NET 2003, SQL Server 2000 Enterprise Manager, UltraEdit32.
I personally tried it out with Microsoft Query Analyzer.
Here’s the link to the download page: SQL Prompt
It’s fairly easy to take an MDB database and convert it to SQL Server, but how about taking an SQL Server database and downloading it into an MDB file (Microsoft Access)?
This article has some code to do just that: http://www.codeproject.com/useritems/SQL_to_Access_Data_Export.asp
Update using an inner join (MSSQL)
Just searched the net trying to figure out how to do an update using an inner join. I figured it was possible, and I was right. Here’s how to do it:
UPDATE Table1
SET Table1.Title = Table2.Title
FROM Table1, Table2
where Table1.ID = Table2.ID
If you are using aliases - for instance if you are updating a table you are joining to itself, use this code:
UPDATE Table1Alias
SET Table1Alias.Title = Table2Alias.Title
FROM Table1 as Table1Alias, Table2 as Table2Alias
where Table1Alias.ID = Table2Alias.ID
Executing Stored Procedures within Stored Procedures (sprocs)
Is it possible to execute a stored procedured within another stored procedure? According to this thread, it appears so. I’ll have to try it out.
Within a stored procedure, you can execute a stored procedure via EXEC OwnerName.StoredProcedureName. Check SQL Server Books Online for details on executing stored procedures. If the stored procedure returns a result set, then you can:
CREATE TABLE #Temp…
INSERT INTO #Temp…
EXEC dbo.SP1 @parm1 = 10
…
DROP TABLE #TempTara Kizer
aka tduggan
If you need a good erudite excuse: http://www.red-gate.com/excusegenerator/Excuses.aspx
Visual Studio Express for Free
Microsoft is celebrating the launch of Visual Studio Express by offering it for free for the first year. This includes their Visual Web Developer 2005 Express Edition. This is a great light-weight tool for .NET 2.0 development. I’ve been using the beta version of it for the past couple of months.
Here’s the link to the download site: http://msdn.microsoft.com/vstudio/express/