1. Search any string in database (in Sps, Functions, Views)
SELECT DISTINCT tSO.Name, tSO.Type_Desc
FROM sys.sysComments tSC
JOIN sys.objects tSO ON tSO.Object_Id = tSC.Id
WHERE tSC.Text LIKE ‘% text %’
2. Get Random Number
SELECT CAST((RAND(CAST(NEWID() AS BINARY(6)))*500) As INT)
3. Update Indexes And Statics
sp_updatestats
4. How to get No. of Rows from each table from Database
CREATE TABLE #counts
(
table_name varchar(255),
row_count int
)
EXEC sp_MSForEachTable @command1=’INSERT #counts (table_name, row_count) SELECT ”?”, COUNT(*) FROM ?’
SELECT table_name, row_count FROM #counts ORDER BY row_count DESC,table_name
5. How to grant exe permission to all proc in Database
USE [@DATABASE NAME]
select ‘GRANT EXECUTE ON [‘+name+’] TO [@username] ‘
from sys.objects
where type =’P’
and is_ms_shipped = 0
Copy all statement and execute them together.
Ms SQL Help 