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

Current Identity value, Identity usage and other identity information

SELECT
QUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) + '.' + QUOTENAME(T.NAME) AS TABLENAME,
C.NAME AS COLUMNNAME,
CASE C.SYSTEM_TYPE_ID WHEN 127 THEN 'BIGINT' WHEN 56 THEN 'INT'
WHEN 52 THEN 'SMALLINT' WHEN 48 THEN 'TINYINT' END AS 'DATATYPE', IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS CURRENTIDENTITYVALUE,
IDENT_INCR(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYINCREMENT,
IDENT_SEED(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYSEED,
CASE C.SYSTEM_TYPE_ID
WHEN 127 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 9223372036854775807
WHEN 56 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 2147483647
WHEN 52 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 32767
WHEN 48 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 255 END AS 'PERCENTAGEUSED'
FROM SYS.COLUMNS AS C
INNER JOIN SYS.TABLES AS T ON T.[OBJECT_ID] = C.[OBJECT_ID] AND C.IS_IDENTITY = 1
ORDER BY PERCENTAGEUSED DESC

Ranking rows of a result set...

Rows can be ranked in a result set [Selection from one table or multiple tables by using joins] by using Rank() function. If two or more rows tie for a rank, tied rows will get the same rank.

Example:
Two temporary tables created, and populated with test data, two queries created one for single table selection and second query to show the results from two tables by using joins.

CREATE TABLE #TEMP
(ID INT
,VAL1 VARCHAR(1)
,VAL2 VARCHAR(10)
,VAL3 VARCHAR(5)
)

CREATE TABLE #TEMP1
(ID1 INT
,VAL11 VARCHAR(1)
,VAL21 VARCHAR(10)
,VAL31 VARCHAR(10)
)

INSERT INTO #TEMP VALUES(1,'A','ABC','TEST1')
INSERT INTO #TEMP VALUES(1,'F','ADC','TEST2')
INSERT INTO #TEMP VALUES(1,'G','ABD','TEST3')
INSERT INTO #TEMP VALUES(2,'H','ABC','TEST4')
INSERT INTO #TEMP VALUES(2,'K','ADC','TEST5')
INSERT INTO #TEMP VALUES(3,'L','ABD','TEST6')

INSERT INTO #TEMP1 VALUES(1,'Z','ABC','TESTING1')
INSERT INTO #TEMP1 VALUES(2,'R','ABC','TESTING4')
INSERT INTO #TEMP1 VALUES(3,'P','ABD','TESTING6')

Single table selection:
SELECT *,RANK() OVER(PARTITION BY ID ORDER BY VAL1) RANK
FROM #TEMP

ID	val1	val2	val3	RANK
1	A	ABC	Test1	1
1	F	ADC	Test2	2
1	G	ABD	Test3	3
2	H	ABC	Test4	1
2	K	ADC	Test5	2
3	L	ABD	Test6	1

Multiple tables selection:
SELECT *,RANK() OVER(PARTITION BY VAL21 ORDER BY VAL1) RANK
FROM #TEMP T
INNER JOIN #TEMP1 T1 ON T.ID=T1.ID1

ID	val1	val2	val3	ID1	val11	val21	val31	RANK
1	A	ABC	Test1	1	Z	ABC	Testing1	1
1	F	ADC	Test2	1	Z	ABC	Testing1	2
1	G	ABD	Test3	1	Z	ABC	Testing1	3
2	H	ABC	Test4	2	R	ABC	Testing4	4
2	K	ADC	Test5	2	R	ABC	Testing4	5
3	L	ABD	Test6	3	P	ABD	Testing6	1

Scalar Configuration Functions

All configuration functions are nondeterministic. This means these functions do not always return the same results every time they are called, even with the same set of input values.

The following scalar functions return information about current configuration option settings,

@@DATEFIRST
Returns the current value, for the session, of SET DATEFIRST.
e.g. SELECT @@DATEFIRST
you can change first day of week setting by using the following command.
SET DATEFIRST [integer]
e.g Set DATEFIRST 5
It will set Firday as first day of week.

@@DBTS
It returns the last-used timestamp value of the current database. A new timestamp value is generated when a row with a timestamp column is inserted or updated.
e.g. SELECT @@DBTS

@@LANGID
Returns the local language identifier (ID) of the language that is currently being used.
e.g. SELECT @@LANGID

To view information about language settings, including language ID numbers, you can use the following system procedure.
EXEC SP_HELPLANGUAGE

You can change language settings as
e.g. SET LANGUAGE 'Italian'

@@SERVERNAME
Returns the name of the local server running SQL Server.
e.g. SELECT @@SERVERNAME

@@LANGUAGE
Returns the name of the language currently being used.
e.g. SELECT @@LANGUAGE
Remaining details will be the same as defined under @@LANGID

@@SERVICENAME
Returns the name of the registry key under which SQL Server is running. @@SERVICENAME returns 'MSSQLSERVER' if the current instance is the default instance; this function returns the instance name if the current instance is a named instance.
e.g. SELECT @@SERVICENAME

@@LOCK_TIMEOUT
Returns the current lock time-out setting in milliseconds for the current session.
It allows an application to set the maximum time that a statement waits on a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is automatically canceled, and an error message is returned to the application.

It returns a value of -1 if SET LOCK_TIMEOUT has not yet been run in the current session.
e.g. SELECT @@LOCK_TIMEOUT

@@SPID
Returns the session ID of the current user process.
It can be used to identify the current user process in the output of sp_who.
e.g. SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

@@TEXTSIZE
Returns the current value of the TEXTSIZE option of the SET statement. This specifies the maximum length, in bytes, of varchar(max), nvarchar(max), varbinary(max), text, or image data that a SELECT statement returns.
The default size is 4096 bytes. The maximum size that @@TEXTSIZE returns is 2^31-1 bytes.

You can change its setting as:

SET TEXTSIZE [integer]

e.g.
Displays current value
SELECT @@TEXTSIZE AS 'Text Size'
Setting new value
SET TEXTSIZE 2048
Displays new value
SELECT @@TEXTSIZE AS 'Text Size'

@@VERSION
Returns version, processor architecture, build date, and operating system for the current installation of SQL Server.
The information returned by @@VERSION is similar to the product name, version, platform, and file data returned by the xp_msver stored procedure, which provides more detailed information.
e.g. SELECT @@VERSION

List of Permission granted to caller [Calling User]

Following statements returns the permission details for the user who is executing the statement.

-- List of Permission at Server Level
SELECT * FROM fn_my_permissions(NULL, 'SERVER')

-- List of Permission at Database Level
SELECT * FROM fn_my_permissions('DB1', 'DATABASE')

-- List of Permission at Object Level
SELECT * FROM fn_my_permissions('Object1', 'OBJECT')
ORDER BY subentity_name, permission_name

It reports:
Entity_Name
Subentity_Name
Permission_Name