Query to find Dependant Objects

Select DISTINCT (OBJECT_SCHEMA_NAME(id)+'.'+ OBJECT_NAME(id)) AS [Object Name] ,(OBJECT_SCHEMA_NAME(depid)+'.'+ OBJECT_NAME(depid)) AS [Is Dependant on], OBJECTPROPERTYEX(id,'BaseType') AS [Object Type],OBJECTPROPERTYEX(depid,'BaseType') AS [Object Type of Is Dependant on] From sys.sysdepends Where OBJECTPROPERTYEX(id,'IsMSShipped')=0 -- To get only user created object

List of SQL Server instances currently installed in your network

To get a list of SQL Server instances currently installed in your network, execute either of the below commands

Exec xp_cmdshell 'SQLCMD /L'
OR
Exec xp_cmdshell 'OSQL /L'


If you want to populate a text file with the results then, open the command prompt (Go to Run, Type cmd and click ok) and type this command and hit enter
SQLCMD /L > C:\ListOfServers.txt
This command will populate the text file ListOfServers.txt with the results obtained from SQLCMD /L

Identity Column

We all know about setting Identity to a column of a table.
By setting identity to a column, we mean that the column should be populated automatically by incrementing numbers based on the seed and increment option we give.
But all these days, i was setting the identity to a column of a table which had more than 2 columns.
Just think of having a table with only one column and setting identity to that Column.

Create Table IdentityTest
(Slno int identity(1,1))

When you run this query, it will create a table named IdentityTest with the column Slno with identity on it.
What next????
The next question would be, how to insert data to this table?
Any idea, on how to insert data to this table?
Here is how you insert data to this table..

Insert Into IdentityTest Default Values

And now after reading this post, you are now familiar with inserting data to a table which has only one column and identity set on it.  :)

Useful DBCC Commands

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

Database Console Command statements are grouped into the following categories.

1. Maintenance

2. Miscellaneous

3. Informational

4. Validation

DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

Some of the Maintenance DBCC Statements are:

DBCC CLEANTABLE

DBCC INDEXDEFRAG

DBCC DBREINDEX

DBCC SHRINKDATABASE

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DBCC SHRINKFILE

DBCC UPDATEUSAGE

Some of the Miscellaneous DBCC Statements are:

DBCC dllname (FREE)

DBCC HELP

DBCC FREESESSIONCACHE 

DBCC TRACEOFF

DBCC FREESYSTEMCACHE

DBCC TRACEON

Some of the Informational DBCC Statements are:

DBCC INPUTBUFFER

DBCC SHOWCONTIG

DBCC OPENTRAN

DBCC SQLPERF

DBCC OUTPUTBUFFER

DBCC TRACESTATUS

DBCC PROCCACHE

DBCC USEROPTIONS

DBCC SHOW_STATISTICS

Some of the Validation DBCC Statements are:

DBCC CHECKALLOC

DBCC CHECKFILEGROUP

DBCC CHECKCATALOG

DBCC CHECKIDENT

DBCC CHECKCONSTRAINTS  

DBCC CHECKTABLE

DBCC CHECKDB

Some of the useful DBCC commands along with their syntax and examples are listed down and are available for download. Click here to download your copy now.
Reference: BOL

Index Recommendations

This Query when executed gives the recomendation to your indexes whether it has to reindexed or defraged.
This query also gives the fragmentation level of your indexes.

Select db_name(DB_ID()) as DBName,SS.name as SchemaName,SO.name as TableName, SI.name as Indexname,index_type_desc as IndexType ,avg_fragmentation_in_percent as FragmentationPercentage,
(case when avg_fragmentation_in_percent between 10 and 30 then 'Defrag'
when avg_fragmentation_in_percent > 30 Then 'Reindex'
Else 'Can be Ignored Currently'
End) as Recomendation
from sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), null, null, null, 'DETAILED') IPS ,
sys.indexes SI ,sys.objects SO , sys.schemas SS
where IPS.index_id=SI.index_id and IPS.object_id=SI.object_id and
SI.object_id=SO.object_id and SO.schema_id=SS.schema_id and
IPS.index_type_desc in ('NONCLUSTERED INDEX', 'CLUSTERED INDEX')
Order by Recomendation desc