Database & Disk space analysis

BEGIN

CREATE TABLE #LOGSPACE
(DBNAME VARCHAR(100)
,LOGSIZE DECIMAL(30,10)
,LOGSPACE DECIMAL(30,10)
,STATUS INT
)

CREATE TABLE #DRIVESPACE
(DRIVE VARCHAR(2)
,DRIVESPACE BIGINT
)

INSERT INTO #LOGSPACE
EXEC ('DBCC SQLPERF(LOGSPACE)')

INSERT INTO #DRIVESPACE
EXEC ('MASTER.SYS.XP_FIXEDDRIVES')

SELECT F.TYPE_DESC DBFILES
,LEFT(F.PHYSICAL_NAME,1) DRIVE
,F.STATE_DESC DBSTATE
,((F.[SIZE]*8)/1024.00)/1024.00 DBCURRENTSIZE
,ISNULL(((A.RESERVED*8)/1024.00)/1024.00 - ((D.LOGSIZE/1024.00) * (D.LOGSPACE/100.00)),(L.LOGSIZE/1024.00) * (L.LOGSPACE/100.00)) DBRESERVEDSIZE
,CASE WHEN F.IS_PERCENT_GROWTH=0 THEN 'PAGES' WHEN F.IS_PERCENT_GROWTH=1 THEN 'PERCENTAGE' END DBGROWTHTYPE
,F.GROWTH DBGROWTHVALUE
,CASE WHEN F.IS_PERCENT_GROWTH=0 THEN ((F.GROWTH*8)/1024.00)/1024.00 WHEN F.IS_PERCENT_GROWTH=1 THEN (((F.[SIZE]*8)/1024.00)*(F.GROWTH/100.00))/1024.00 END DBGROWTHSIZE
,CASE WHEN F.MAX_SIZE=0 THEN 'FIXED SIZE' WHEN F.MAX_SIZE=-1 THEN 'UNLIMITED' ELSE CONVERT(VARCHAR,((CONVERT(BIGINT,F.MAX_SIZE)*8)/1024.00)/1024.00) END MAX_SIZE
,DS.DRIVESPACE/1024.00 FREESPACEONDRIVE
INTO #SPACEANALYSISREPORT
FROM SYS.DATABASE_FILES F
INNER JOIN #DRIVESPACE DS ON DS.DRIVE = LEFT(F.PHYSICAL_NAME,1)
LEFT OUTER JOIN #LOGSPACE L ON L.DBNAME = DB_NAME() AND F.TYPE_DESC='LOG'
LEFT OUTER JOIN #LOGSPACE D ON D.DBNAME = DB_NAME() AND F.TYPE_DESC='ROWS'
LEFT OUTER JOIN
(SELECT DB_ID() DATABASE_ID, 'ROWS' TYPE_DESC,SUM(TOTAL_PAGES) RESERVED, SUM(USED_PAGES) USED FROM SYS.ALLOCATION_UNITS) A ON A.TYPE_DESC=F.TYPE_DESC

SELECT * FROM #SPACEANALYSISREPORT

DROP TABLE #LOGSPACE
DROP TABLE #DRIVESPACE
DROP TABLE #SPACEANALYSISREPORT

END

Getting All Disks Free Space information on an instance

you must define linked servers on the instance executing this code if it has to calculate the free disk space information from remote servers.

BEGIN
SET NOCOUNT ON
DECLARE @TOTALCOUNT INT
,@I INT
,@SERVER_NAME NVARCHAR(100)
,@DESCR NVARCHAR(100)
,@STMT NVARCHAR(200)

/* Temporary Table holding Server names */
CREATE TABLE #SERVERS
(PID INT IDENTITY(1,1)
,SERVER_NAME VARCHAR(100)
,DESCR VARCHAR(100))

/* List of All Servers under action */
INSERT INTO #SERVERS(SERVER_NAME,DESCR)
VALUES('SERVER1','FIRST TEST SERVER')

INSERT INTO #SERVERS(SERVER_NAME,DESCR)
VALUES('SERVER2','SECOND TEST SERVER')

/* Total Number of Servers */
SELECT @TOTALCOUNT=COUNT(*) FROM #SERVERS

/* Temporary global table holding Server, Drive and its free space information*/
CREATE TABLE ##MYFREESPACE
(SERVER_NAME VARCHAR(30)
,DRIVE VARCHAR(50)
,FREESPACE BIGINT)

/* Iteration to perform action on each server */
SET @I=1
WHILE (@I<=@TOTALCOUNT)
BEGIN
SELECT @SERVER_NAME = SERVER_NAME,@DESCR=DESCR FROM #SERVERS WHERE PID=@I

/* Try & Catch Block in case of any error */
BEGIN TRY
INSERT INTO ##MYFREESPACE(SERVER_NAME,DRIVE) VALUES(@SERVER_NAME,@DESCR)

SET @STMT = LTRIM(RTRIM(@SERVER_NAME)) + '.MASTER.SYS.XP_FIXEDDRIVES'

INSERT INTO ##MYFREESPACE(DRIVE,FREESPACE)
EXEC SP_EXECUTESQL @STMT
END TRY

BEGIN CATCH
INSERT INTO ##MYFREESPACE(SERVER_NAME,DRIVE) VALUES(@SERVER_NAME,LEFT(CONVERT(VARCHAR,@@ERROR)+'-'+ERROR_MESSAGE(),50))
END CATCH

SET @I = @I + 1

END

/* Send Email by attaching the whole manipulated information */
EXEC MSDB.DBO.SP_SEND_DBMAIL
@PROFILE_NAME = 'TEST ALERTS',
@RECIPIENTS = 'TEST@TEST.COM',
@QUERY = ' SELECT ISNULL(SERVER_NAME,'''') SERVER_NAME, DRIVE,CAST(ISNULL(FREESPACE,0.00)/1024.00 AS DECIMAL(20,3)) FREESPACE_GB FROM ##MYFREESPACE' ,
@SUBJECT = 'FREE DISK SPACE INFORMATION',
@ATTACH_QUERY_RESULT_AS_FILE = 0 ;

/* Drop Temporary Tables */
DROP TABLE #SERVERS
DROP TABLE ##MYFREESPACE

END

How to get list of all stored procedures?

You can use following stored procedure with four optional input parameters to get a list of all stored procedures in the current environment.

EXEC SP_STORED_PROCEDURES @sp_name = 'procedure name'
, @sp_owner = 'schema name'
, @sp_qualifier = 'database name'
, @fUsePattern = 'fUsePattern'

Note:

1. All parameters are optional.
2. @sp_name and @sp_owner support wildcard pattern matching (underscore “_“, percent “%” and brackets []).
3. @sp_qualifier it will have null or current database name only.
4. @fUsePattern, it can be 0 (wildcard pattern matching is off) or 1 (wildcard pattern matching is on), by default it is 1.

Examples:

1. To see complete list of all stored procedures in current database
   EXEC SP_STORED_PROCEDURES
2. To see complete list of procedures, procedure name starting with “fn” characters
   EXEC SP_STORED_PROCEDURES @sp_name = 'fn%'
3. To see complete list of procedure, procedure name starting with “fn” and schema name starting with “s” characters
   EXEC SP_STORED_PROCEDURES @sp_name = 'fn%', @sp_owner = 's%'

Change the name of a Database, Table or Column

You can use the following procedure to change database, table, index or column name.

SP_RENAME 'Object_Old_Name','Object_New_Name','Object_Type'

Object Type can be DATABASE, OBJECT, INDEX, COLUMN and USERDATATYPE.

To Change Database name:
It will change the database name from DB1 to DB1_New.
EXEC SP_RENAME 'DB1','DB1_New','DATABASE'

To change Table name:
It will update the table name from Table1 to Table1_New.

EXEC SP_RENAME 'Table1','Table1_New','OBJECT'


To change Column name:
It will change column name val1 in Table1 to val1_new.

EXEC SP_RENAME 'Table1.val1','val1_new','COLUMN'

Searching a Table in all databases on Current Server

You can use Information_Schema view Tables to see the table list in the current DB as:
Select * From DB1.Information_Schema.Tables

we will use the above Sql query with the following stored procedure to search a table.

Procedure: dbo.sp_MSforeachdb
Description: This procedure will execute the attached string as a command against every database on the server. Any question mark(?) , within the string will be replaced by every database name.

Example:
so you can use it to find a table name in every database as:

Exec dbo.sp_MSforeachdb 'select ''?'', * from [?].INFORMATION_SCHEMA.TABLES where table_name like ''authors'' '

Test the connection to a Linked Server

Procedure: SP_TESTLINKEDSERVER

Syntax:
sp_testlinkedserver @servername = servername

@Servername: Linked servername

Note: Caller must have appropriate login mapping.

e.g.
Exec sp_testlinkedserver SQL1

Get List of Linked Servers on Curent/Local Server

Procedure: SP_LINKEDSERVERS

Description: Returns the list of linked servers defined in the local server.

It returns:
Column name - Description
SRV_NAME - Name of the linked server.
SRV_PROVIDERNAME - Friendly name of the OLE DB provider managing access to the specified linked server.
SRV_PRODUCT - Product name of the linked server.
SRV_DATASOURCE - OLE DB data source property corresponding to the specified linked server.
SRV_PROVIDERSTRING - OLE DB provider string property corresponding to the linked server.
SRV_LOCATION - OLE DB location property corresponding to the specified linked server.
SRV_CAT - OLE DB catalog property corresponding to the specified linked server.

Database Level Principals List : System Stored Procedure

Procedure Name: sp_helpuser
Definition: Reports information about database-level principals in the current database.
Syntax: sp_helpuser [ [ @name_in_db = ] 'security_account' ]

[ @name_in_db = ] 'security_account'
Is the name of database user or database role in the current database. security_account must exist in the current database. security_account is sysname, with a default of NULL. If security_account is not specified, sp_helpuser returns information about all database principals.

It returns:
Username
Groupname [User Role]
LoginName [Login for user]
DefDBName [Default database name]
DefSchemaName [Default Schema Name]
UserId [ID of username in current database]
SID [User Security Identification Number]

Example:
Exec Sp_Helpuser
it will display the stats for all available users in the current database

Or

Exec sp_helpuser 'dbo'
it will display the stats only for dbo user.