Find row count of all the tables in a Database

Here is a quick and easy way to find the row count of all the tables in a database.
Before getting the row count, you have to update the usage of your database.

/*This Query works for SQL 2000, SQL 2005 and SQL 2008*/

DBCC UPDATEUSAGE('Your DB Name') WITH COUNT_ROWS
GO
Select object_name(id) as [Table Name],rowcnt as [RowCount] from sysindexes
where indid < 2 AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
Order by object_name(id)

/*This Query works for SQL 2005 and SQL 2008 as it uses DMV*/

DBCC UPDATEUSAGE('Your DB Name') WITH COUNT_ROWS
GO
Select Distinct Object_Name(object_id) as [Table Name], row_count as [RowCount] from sys.dm_db_partition_stats where OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
Order by Object_Name(object_id)

Finding Identity Key Columns in SQL Server 2005

To find the Identity Key Cloumns in a particular database

SELECT Object_Name(Object_ID)  AS TableName,
Name AS ColumnName,
Seed_Value AS SeedValue,
Increment_Value AS IncrementValue,
ident_current(Object_Name(Object_ID)) AS CurrentValue,
Last_Value AS LastValue
FROM sys.identity_columns
Order by TableName

To find the Identity Key Cloumns in all databases

EXEC sp_msforeachdb 'Use ?

SELECT ''?'' AS DatabaseName, Object_Name(Object_ID) AS TableName,
name AS ColumnName,
Seed_Value AS SeedValue,
Increment_Value AS IncrementValue,
ident_current(Object_Name(Object_ID)) AS CurrentValue,
Last_Value AS LastValue
FROM sys.identity_columns
Order by TableName'

Find out who has changed what

There are many cases where the objects in a database gets changed without any information to the admins. This may be accidenatal or ----- :) .
So if your server instance has the default trance enabled, then you can find out who has changed what in you databases.

1. This Query gives the trace flie path.
    SELECT * FROM ::fn_trace_getinfo(0)
2. Execute the Below Query to get the data from trace file. The filters can also be applied to the below query to get the exact data
    SELECT * FROM ::fn_trace_gettable (':\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_59.trc', default)
3. Map the trace table to find what type of event has happened.
    SELECT TE.name, T.*

    FROM dbo.temp T -- table that contains the trace results
    JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
4. To get the list of possible events
    SELECT * FROM sys.trace_events where name like '%alter%' ORDER BY trace_event_id

Find the size of all databases at once

You may run into cases where you have to find the size of all the databases in a server in less time...
This will be easy and quick when you have less databases on the box.
What happens if the box has more number of databases??? Here is a quick solution for it...

Run the below Query and get your results in less time and in one shot.

EXEC sp_msforeachdb 'Use [?]
Declare @dbsize float
Declare @logsize float
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles
select ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2) + '' MB'') AS [Size of ?]'

Finding Cluster Nodes or Cluster Name

Here is a Query to find the Cluster Nodes or Cluster Name using SQL server 2005.

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

This helps in  finding which node the instance is currently running.