Find all tables without Triggers in SQL Server

Sometime you need to know all tables without trigger before adding, changing  or dropping column in a table.  Here are two ways to know that:

SELECT S1.Name FROM SysObjects S1 LEFT JOIN SysObjects S2 ON
S1.ID =S2.Parent_Obj
AND S2.XType = 'TR'
WHERE S2.Name IS NULL
AND S1.XType = 'U'
ORDER BY S1.Name

SELECT T.TABLE_Name FROM INFORMATION_SCHEMA.TABLES T
LEFT JOIN (SELECT OBJECT_Name(o.Parent_Obj) AS TableName
FROM SysObjects O
WHERE OBJECTPROPERTY(O.[ID], 'IsTrigger') = 1
) TR ON T.TABLE_Name= TR.TableName
WHERE TR.TableName IS NULL
AND TABLE_TYPE ='BASE TABLE'
ORDER BY T.TABLE_Name

Backup History

Here is a useful script to get the backup History for all databases in SQL Server:

SELECT S.Server_Name, S.Recovery_Model, S.Database_Name, M.Physical_Device_Name,
    CASE S.[Type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType,
    S.Backup_Start_Date, S.Backup_finish_Date,
    CAST(DATEDIFF(second, S.Backup_Start_Date,S.Backup_Finish_Date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeElapsed,
    CAST(CAST(S.Backup_Size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Backup_Size,
    CAST(S.Dirst_LSN AS VARCHAR(50)) AS First_LSN,
    CAST(S.Last_LSN AS VARCHAR(50)) AS Last_LSN
FROM msdb.dbo.BackupMediaFamily AS M 
INNER JOIN msdb.dbo.BackupSet AS S ON M.Media_Set_ID = S.Media_Set_ID
ORDER BY S.Backup_Start_Date DESC

Unable to remove SQL Server 2008 after removing from cluster

I was unable to uninstall SQL Server after it was removed from Failover Cluster Manager. It was logging below error in application log:

Product: Microsoft SQL Server 2008 Database Engine Services — Error 25012. There was an error attempting to remove the configuration of the product which prevents any other action from occuring. The current configuration of the product is being cancelled as a result.

I had followed below steps to uninstall SQL Server:

1. Open Registry Editor (Start -> run -> type regedit)

2. Navigate to the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\ClusterState\SQL_Engine_Core_Inst

3. Change the value of this “SQL_Engine_Core_Inst” key from 1 to 0.

4. Uninstall as normal through Programs and Features

Finding a Table in all databases on Server

dbo.sp_MSforeachdb procedure will execute the attached string as a command against every database on the server. Any question mark(?) , within the string will be replaced by every database name.
We can use the Information_Schema view Tables to see the tables list in the current DB:

Select * From DB1.Information_Schema.Tables

So by combining both SP and View we can search for a Table in all databases in Server

EXEC dbo.sp_MSforeachdb 'SELECT ''?'', * FROM [?].INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE ''Items'' '

Find Table with Max. rows & Size

You can use the below query to get all tables with No of rows and size. User required View Database State permission on the Server to use below DMV

SELECT SN.Name AS [Schema Name], ST.Name AS [Table Name], SI.Name AS [Index Name], PS.Reserved_Page_Count * 8 AS [Total Space Consumed (in KB)], PS.Used_Page_Count * 8 AS [Used Space (in KB)], (PS.Reserved_Page_Count - PS.Used_Page_Count) * 8 AS [Free Space (in KC)], CASE WHEN PS.Index_ID IN (0, 1) THEN PS.Row_Count ELSE NULL END AS [Row Count]
FROM SYS.DM_DB_PARTITION_STATS AS PS INNER JOIN SYS.OBJECTS AS ST ON ST.OBJECT_ID = PS.OBJECT_ID INNER JOIN SYS.SCHEMAS AS SN ON SN.SCHEMA_ID = ST.SCHEMA_ID LEFT JOIN SYS.INDEXES AS SI ON SI.OBJECT_ID = PS.OBJECT_ID AND SI.INDEX_ID = PS.INDEX_ID
WHERE ST.IS_MS_SHIPPED = 0
ORDER BY [Total Space Consumed (in KB)] DESC