ROB SYMONDS

T-SQL

SQL Server GO Keyword

By

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

By

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

By 

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