Tag Archive - T-SQL

SQL Server GO Keyword

October 16, 2008 in Technology with 0 Comments

You see the word GO in a lot of T-SQL scripts but do you actually know what it is or how to use it?

GO is actually not a part of T-SQL and is never passed to SQL Server. It is used by clients such as osql, sqlcmd, SSMS and Query Analyzer to control how commands are batched and sent to the server.

Read more about the GO keyword.

Performance: EXISTS vs LIKE

September 21, 2008 in Technology with 0 Comments

The EXISTS and NOT EXISTS operators can often use an index whereas the LIKE and NOT LIKE operators cause a table scan. This makes EXISTS and NOT EXISTS tend to be more efficient.

Get Space Used By Files in All Databases

June 19, 2008 in Technology with 0 Comments 

DECLARE @dbs TABLE (dbname NVARCHAR(200))
DECLARE @wrkStatement NVARCHAR(200)
DECLARE @wrkSql NVARCHAR(4000)
INSERT INTO @dbs(dbname)
	SELECT name
	FROM master..sysdatabases
	WHERE name NOT IN ('model', 'master', 'msdb')
	ORDER BY name
CREATE TABLE #tmp123_results (
	[Db] NVARCHAR(100),
	[FileId] INT,
	[LogicalName] NVARCHAR(655),
	[AllocatedMB] DECIMAL(12, 2),
	[UsedMB] DECIMAL(12, 2),
	[FreeMB] DECIMAL(12, 2),
	[PercentUsed] DECIMAL(12, 2),
	[PercentFree] DECIMAL(12, 2),
	[PhysicalName] NVARCHAR(655)
)
DECLARE @curr_db NVARCHAR(100)
DECLARE @crsSelect CURSOR
SET @crsSelect = CURSOR FOR
	SELECT dbname
    FROM @dbs
OPEN @crsSelect
FETCH NEXT FROM @crsSelect INTO @curr_db
WHILE @@FETCH_STATUS = 0
    BEGIN
		SET @wrkStatement = '['+@curr_db+ '].dbo.sp_executesql'
		SET @wrkSql = 'INSERT INTO #tmp123_results(Db,FileId,LogicalName,AllocatedMB,UsedMB,FreeMB,PercentUsed,PercentFree,PhysicalName)
			SELECT
			DB = '''+@curr_db+''',
			a.FILEID,
			NAME =	a.NAME,
			ALLOCATED_MB = convert(decimal(12,2),round(a.size/128.000,2)),
			USED_MB = convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
			FREE_MB = convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
			PercentUsed = 100 * (convert(decimal(12,2),round((fileproperty(a.name,''SpaceUsed''))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
			PercentFree = 100 * (convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
			FILENAME = a.FILENAME
			FROM [' + @curr_db + '].dbo.sysfiles a'	
		EXEC @wrkStatement @wrkSql
        FETCH NEXT FROM @crsSelect INTO @curr_db
    END
CLOSE @crsSelect
DEALLOCATE @crsSelect
SELECT * FROM #tmp123_results ORDER BY Db, FileId
DROP TABLE #tmp123_results