Space used in tempdb by each Session

SELECT DB_NAME(DATABASE_ID) DBNAME
,SP.SESSION_ID
,S.LOGIN_NAME
,S.HOST_NAME
,(SUM(USER_OBJECTS_ALLOC_PAGE_COUNT)*8)/1024.00 ALLOCATED_SPACE_MB
,(SUM(USER_OBJECTS_DEALLOC_PAGE_COUNT)*8)/1024.00 DEALLOCATED_SPACE_MB
FROM TEMPDB.SYS.DM_DB_SESSION_SPACE_USAGE SP
INNER JOIN TEMPDB.SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = SP.SESSION_ID AND S.HOST_NAME IS NOT NULL
GROUP BY DB_NAME(DATABASE_ID),SP.SESSION_ID,S.LOGIN_NAME, S.HOST_NAME
ORDER BY ALLOCATED_SPACE_MB DESC

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

how to view association among File Groups, System Logical and Physical Files and Tables

You can use this code to view file group, logical filename and physical filename associated to a table. It can be beneficial if you have large number of database files and want to see objects (tables) in a file or to view the file associated to a table.

To see whole list of tables in each data file
A table can have only one value at a time in index table (0 or 1), 1 for clustered index and 0 for heap, so you can use both values, if a table has clustered index it will not have heap associated value in index table and vice versa.

SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SI.INDEX_ID IN (0,1)

To see whole list of tables in a specific data file
You can get file id or file name or logical file name from below query or you can use FILE_ID() function if you know logical file name.

SELECT FILEID, FILENAME, NAME FROM SYS.SYSFILES

SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SF.FILEID = FILE_ID('Logical File Name')

To see filename for a table
SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SI.OBJECT_ID = OBJECT_ID('Table Name')

Table's rows count without using COUNT() function

You can get table's total number of rows as:

SELECT SUM(ROWS) AS Total_Rows
FROM SYS.SYSINDEXES
WHERE ID=OBJECT_ID('Table1')
AND INDID IN (0,1)

OBJECT_ID('Table1'):
This function will return the object id for table "Table1" or any other specified table.

INDID:
It can be 0 for heap and 1 for clustered index, and it will have only one value at a time 0 or 1, greater than 1 values are for non-clustered indexes.

Determining Index fragmentation / when to rebuild / when to reorganize

Index fragmentation:
When data is inserted, updated or deleted in a table, if clustered or non-clustered indexes exist on that table, they must be maintained to reflect the changes. The maintenance of these indexes eventually will cause the indexes less efficient. This inefficiency leads to index fragmentation.

We have two types of index fragmentation:
Internal Fragmentation
External Fragmentation

In result of DELETE operation, spaces occurs in the underlying index page, so when index page is not full as it should be, it leads to internal fragmentation.

Sometimes when INSERT or UPDATE operation occurs, database engine creates additional index page to accommodate this operation, a page split occurs and a new page created by maintaining the logical order, as it does not maintain the physical order of index pages. So this variation in logical and physical ordering of index pages called external fragmentation.

SQL Server maintains the counters or values to keep track indexes internal and external fragmentation, so you can evaluate by using these indicators, when to rebuild and when to reorganize the indexes to increase its efficiency.

You can get these stats from a dynamic management function that is replacement of DBCC SHOCONTIG statement:

SYS.DM_DB_INDEX_PHYSICAL_STATS(database_id/Null, object_id/Null, index_id/Null/0, partition_number/Null, mode/Null/Defualt)

database_id/Null: ID of the database, you can use DB_ID(‘database_name’) or DB_ID() to get the current database id. If you specify Null then it will return the information for all databases, and you must supply object_id, index_id and partition_number with Null values.
object_id/Null: ID of base table or view. You can use Object_ID(‘table/view name’) to get object id. If you specify Null, it will return the information of all table/view of current database, you must supply index_id and partition_number with Null values.
index_id/Null/0: ID of index. You can specify Null, it will return the information of all indexes for specified base table or index, you must supply partition_number with Null value. 0 index_id is for heap tables.
partition_number/Null: partition number of index/heap or Null to return the information for all partitions.
mode/Null/Default: mode specifies the scan level that is used to obtain statistics. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED or DETAILED. The default is LIMITED.


Now you have to decide when to rebuild (recreating the current indexes) the indexes and when to reorganize (de-fragmentation of the current indexes) them, you have to observe the values of avg_fragmentation_in_percent and avg_page_space_used_in_percent columns returned by sys.dm_db_index_physical_stats DMF.


If avg_fragmentation_in_percent greater than 15 OR avg_fragmentation_in_percent less than 60 then you have to rebuild or recreate the indexes as:

ALTER INDEX ALL ON Table_Name REBUILD


If avg_page_space_used_in_percent between 60 and 75 Or avg_fragmentation_in_percent between 10 and 15 then you have to reorganize the indexes as:

ALTER INDEX ALL ON Table_Name REORGANIZE

Example:

SELECT
DB_NAME(DATABASE_ID)
,OBJECT_NAME(OBJECT_ID)
,INDEX_ID
,INDEX_TYPE_DESC
,AVG_FRAGMENTATION_IN_PERCENT
,AVG_PAGE_SPACE_USED_IN_PERCENT
,CASE WHEN AVG_FRAGMENTATION_IN_PERCENT >15 OR AVG_PAGE_SPACE_USED_IN_PERCENT<60 THEN 'REBUILD INDEX' END AS INDEX_REBUILD_STATE
,CASE WHEN (AVG_FRAGMENTATION_IN_PERCENT >10 AND AVG_FRAGMENTATION_IN_PERCENT<15) OR
(AVG_PAGE_SPACE_USED_IN_PERCENT>60 AND AVG_PAGE_SPACE_USED_IN_PERCENT<75) THEN 'RE-ORGANIZE INDEX' END AS INDEX_REORGANIZE_STATE
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), OBJECT_ID('TEST'), NULL, NULL, 'DETAILED')
WHERE INDEX_ID<>0

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'