Few more commands and queries

---// This is to add new ndf file for any database in any particular location

ALTER DATABASE [TestDB]
ADD FILE
(
      NAME = TestDB_3,
      FILENAME = 'C:\Database\Data\TestDB_3.ndf'
)
TO FILEGROUP [SecondaryFG]
GO 

---//To make database clone with new database.

dbcc clonedatabase('Client_Dev',Client_Dev_Clone')
DBCC CLONEDATABASE (Client_Dev, Client_Dev_Clone) WITH NO_STATISTICS

---// How to check fragmentation percent to do indexing on DB tables. 

SELECT OBJECT_NAME(ips.OBJECT_ID)
 ,i.NAME
 ,ips.index_id
 ,index_type_desc
 ,avg_fragmentation_in_percent
 ,avg_page_space_used_in_percent
 ,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
 AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC


---// After deleteing records from the table, to reseed the identity column value..

SELECT IDENT_CURRENT('TABLE_NAME')


DBCC CHECKIDENT ('TABLE_NAME', RESEED, 14);

---// Database Connection count 

select  db_name(dbid) , count(*) 'connections count'
from master..sysprocesses
where spid > 50 and spid != @@spid
group by  db_name(dbid)
order by count(*) desc

select  loginame , nt_username, count(*) 'Connections count'
from master..sysprocesses
where spid > 50 and spid != @@spid
group by  loginame , nt_username

order by count(*) desc

---// Find Table column level dependencies 

SELECT DISTINCT so.name FROM sysobjects so
INNER JOIN syscomments sc ON sc.id = so.id
WHERE  so.[type] = 'P'GetAgentPublisherSettings
AND so.[name] LIKE 'st_%'
AND sc.[text] LIKE '%tbl_company_basic%'
AND sc.[text] LIKE '%service_opted%'
ORDER BY so.name


SELECT DISTINCT object_name(so.id) TableName, sc.name ColumnName, OBJECT_NAME(sd.id) DependentObjectName,
(SELECT xtype FROM sysobjects so WHERE so.id = sd.id) Object_Type
FROM sysobjects so 
INNER JOIN syscolumns sc ON so.id = sc.id
INNER JOIN sysdepends sd ON so.id = sd.depid and sc.colid = sd.depnumber
WHERE object_name(so.id) = 'tbl_company_basic'
AND sc.name = 'service_opted'

order by object_name(so.id), Object_Type