Check if a table name exists in stored procedures

 use [yorDB]
 GO


SELECT [Scehma]=schema_name(o.schema_id), o.Name, o.type

FROM sys.sql_modules m

INNER JOIN sys.objects o

ON o.object_id = m.object_id

WHERE m.definition like '%tablename%'









GO

Script: finding the object_id from the page_id

I had a situation where there was a page split ( monitored through Extended Events) and the page_id was 46565645. I wanted to find the table so that I could work with the fill factor of the indexes.   First I turned the following trace ON     DBCC TRACEON (3604); DBCC PAGE (6, 1, 295, 0); DBCC TRACEOFF (3604); GO

 

PAGE: (1:295)   BUFFER:   BUF @0x00000004FD8C7980   bpage = 0x00000004A2D14000          bhash = 0x0000000000000000          bpageno = (1:295) bdbid = 6                           breferences = 0                     bcputicks = 0 bsampleCount = 0                    bUse1 = 55116                       bstat = 0x809 blog = 0x15ab215a                   bnext = 0x0000000000000000            PAGE HEADER:   Page @0x00000004A2D14000   m_pageId = (1:295)                  m_headerVersion = 17                m_type = 17 m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200 m_objId (AllocUnitId.idObj) = 84    m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594043432960 Metadata: PartitionId = 72057594039042048                                Metadata: IndexId = 0 Metadata: ObjectId = 245575913      m_prevPage = (0:0)                  m_nextPage = (0:0) pminlen = 8008                      m_slotCnt = 1                       m_freeCnt = 83 m_freeData = 8107                   m_reservedCnt = 0                   m_lsn = (35:200:9) m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0 m_tornBits = 1093512791             DB Frag ID = 1                        Allocation Status   GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL                        DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED     In the above result Metadata: ObjectId = 245575913    gave me the object_id   I was able to find the table name with the page splits once I had the object_id .

Drop the user defined objects

--remove all user defined tables exec sp_MSforeachtable @command1 = 'Drop table ?';
--remove all the user defined stored procedures from the database
declare @procname nvarchar(max)
declare curProc Cursor for SELECT [name] from sys.objects where type = 'P'
Open curProc Fetch next from curProc into @procName
While @@fetch_Status = 0
Begin
Exec('drop procedure ' + @procName)
Fetch Next From curProc Into @procName
End
Close curProc
Deallocate curProc --drop all user defined views

Declare @viewName varchar(500)
Declare curView Cursor For Select [name] From sys.objects where type = 'v'
Open curView Fetch Next From curView Into @viewName
While @@fetch_status = 0
Begin
Exec('drop view [' + @viewName+']')
Fetch Next From curView Into @viewName
End
Close curView
Deallocate curView

--Remove all Triggers
-- drop all user defined triggers
Declare @trgName varchar(500)
Declare curTrig Cursor For Select [name] From sys.objects where type = 'tr'
Open curTrig Fetch Next From curTrig Into @trgName
While @@fetch_status = 0 Begin Exec('drop trigger ' + @trgName)
Fetch Next From curTrig Into @trgName
End
Close curTrig
Deallocate curTrig --Remove all functions -- drop all user defined scalar-valued functions
Declare @FncName varchar(500)
Declare curFunc Cursor For Select [name] From sys.objects where type = 'FN'
Open curFunc
Fetch Next From curFunc Into @FncName
While @@fetch_status = 0
Begin
Exec('drop function ' + @FncName)
Fetch Next From curFunc Into @FncName
End Close curFunc
Deallocate curFunc

-- drop all user defined table-valued functions
Declare @FncName1 varchar(500)
Declare curFunc Cursor For Select [name] From sys.objects where type = 'TF'

Open curFunc
Fetch Next From curFunc Into @FncName1
While @@fetch_status = 0
Begin
Exec('drop function ' + @FncName1)
Fetch Next From curFunc Into @FncName1
End
Close curFunc
Deallocate curFunc