DBA

Script Out All Indexes and Primary Keys from a Database

by

USE: To generate T-SQL code for all indexes and PK constraints from a database. CLEAR $sSRCServer = “TPW520” $sSRCDatabase = “iSQLPS” $oSO = New-SqlScriptingOptions $oSO.ClusteredIndexes = $true $oSO.Indexes = $true $oSO.NonClusteredIndexes = $true $oSO.IncludeIfNotExists = $true $oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable foreach ($oTable in $oTables){ $oT = $oTable.Indexes foreach ($oIndex in $oT) { $DDL = … Read more

Create Time String

by

USE: This function will return a string representation of time. CREATE FUNCTION [dbo].[fnCreateTimeString] ( @seconds int) RETURNS varchar(75) AS BEGIN DECLARE @h int, @m int, @s int, @secs int, @BuildDate varchar(75), @hour varchar(75), @minute varchar(50), @second varchar(25) SELECT @secs = @seconds SELECT @h = @secs / 3600 SELECT @secs = @secs – (@h * 3600) … Read more

Table Constraints

by

USE: To display all table constraints from a database. SET NOCOUNT ON SELECT A.name [TableName] , B.name [ColumnName] , C.name [ConstraintName] , C.[type] [ConstraintType] , A.crdate FROM sysobjects A INNER JOIN sys.columns B ON A.id = B.object_id INNER JOIN sys.key_constraints C ON A.id = C.parent_object_id WHERE A.Xtype = ‘U’ AND B.is_identity = 1 ORDER BY … Read more

Database Information

by

USE: To display database detail information. SELECT @@SERVERNAME [ServerName], MF.data_space_id, MF.database_id, MF.[type], MF.[type_desc], MF.[name] [DBName], MF.[physical_name] [FilePath], MF.[state_desc], CAST((MF.[size] * 8.0) / 1024.0 AS numeric(19,2)) [FileSize (MB)], MF.[max_size], MF.[growth], DB.[create_date], DB.[compatibility_level], DB.[collation_name], DB.[user_access_desc], DB.[state_desc], DB.[recovery_model] FROM sys.master_files MF INNER JOIN sys.databases DB ON MF.database_id = DB.database_id LEFT JOIN sys.data_spaces DS ON MF.data_space_id = DS.data_space_id LEFT … Read more

Database Size

by

USE: To display database file size information at the server level. SELECT @@SERVERNAME [ServerName], MF.database_id, DB.[name] [DBName], SUM(CAST((MF.[size] * 8.0) / 1024.0 AS numeric(19,2))) [Total Space Usage (MB)] FROM sys.master_files MF INNER JOIN sys.databases DB ON MF.database_id = DB.database_id GROUP BY MF.database_id, DB.[name] ORDER BY MF.database_id GO  

Capture sp_who2

by

USE: To capture the result from executing sp_who2 system stored procedure. SET NOCOUNT ON IF OBJECT_ID(‘tempdb..#SP_WHO2’) IS NOT NULL DROP TABLE #SP_WHO2 GO CREATE TABLE #SP_WHO2 ( SPID varchar(128) , [Status] char(12) , [Login] varchar(128) , [HostName] varchar(128) , [BlkBy] varchar(128) , [DBName] varchar(128) , [Command] varchar(128) , [CPUTime] varchar(128) , [DiskIO] varchar(128) , LastBatch … Read more

Database File Growth

by

USE: To find out recent database files growth. IF(SELECT CONVERT(int,value_in_use) FROM sys.configurations WHERE [name] = ‘default trace enabled’ ) = 1 BEGIN declare @curr_tracefilename varchar(500) ; declare @base_tracefilename varchar(500) ; declare @indx int ; SELECT @curr_tracefilename = [path] FROM sys.traces where is_default = 1 ; SET @curr_tracefilename = REVERSE(@curr_tracefilename); SELECT @indx = PATINDEX(‘%%’, @curr_tracefilename) ; … Read more