How to: search for a word or column name across the Procedure, Function, Trigger, View

 

DECLARE @SearchPhrase nvarchar(1000)   SET @SearchPhrase = N'backup'   -- Place your search string   SELECT DISTINCT sysobjects.name AS [Object Name], CASE   WHEN sysobjects.xtype = 'P' THEN 'Stored Proc'   WHEN sysobjects.xtype = 'TF' THEN 'Function'   WHEN sysobjects.xtype = 'TR' THEN 'Trigger'   WHEN sysobjects.xtype = 'V' THEN 'View' END as [Object Type], (SELECT ParentTable.[Name] FROM sysobjects ParentTable WHERE ParentTable.id = sysobjects.Parent_obj) ParentTable FROM sysobjects,      syscomments WHERE sysobjects.id = syscomments.id AND sysobjects.type in ('P','TF','TR', 'V') --AND sysobjects.category = 0  --- To restrict only user defined object AND CHARINDEX(@SearchPhrase, syscomments.text) > 0

How to: find when the database last got restored?

 

   1:  SELECT destination_database_name,max(restore_date) as restore_date

   2:  FROM msdb.dbo.restorehistory 

   3:  WHERE destination_database_name = 'testDB’  ---- Place your database name here

   4:  GROUP BY destination_database_name

The result will look like;

destination_database_name     restore_date

testDB                        2013-04-29 11:49:19.940

SQL 2008 Management Studio does not allow to save changes to tables

 

In SQL Server 2008 Management Studio,

We can not do

   •Change the Allow Nulls setting for a column

   •Reorder the columns

   •Change the column data type

    •Add a new column

How to fix / Resolution:

    •Go to Tools -> Options -> Designers -> Tables and Designers

     •Uncheck the Prevent Saving Changes that require table re-creation