Tuesday, April 30, 2013

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
DISTINCT sysobjects.name AS [Object Name],
  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,
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