–First, the following code pins the “authors” table in pubs:
DECLARE @db_id int, @tbl_id int
USE pubs
SET @db_id = DB_ID(‘pubs’)
SET @tbl_id = OBJECT_ID(‘pubs..authors’)
DBCC PINTABLE (@db_id, @tbl_id)
SELECT @tbl_id
–Next, a table is not really pinned until after it is retrieved from disk into the buffer, so you need to do this with the following command:
SELECT * FROM authors
–Now to see if the authors table is pinned or not, you can run the following code. If the table is pinned, you will get a message that it is, if it is not pinned, then you won’t get any return information about pinned tables.
IF OBJECTPROPERTY (object_id(‘authors’),’TableIsPinned’) = 1
PRINT ‘TableIsPinned’
–When you are done, you will want to unpin your table. To do so, run this example code:
DECLARE @db_id int, @tbl_id int
USE pubs
SET @db_id = DB_ID(‘pubs’)
SET @tbl_id = OBJECT_ID(‘pubs..authors’)
DBCC UNPINTABLE (@db_id, @tbl_id)
Of course, this example code only will check one table at a time. To check all your tables, run the following code:
SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ‘BASE TABLE’ AND OBJECTPROPERTY(object_id(TABLE_NAME), ‘TableIsPinned’) > 0
Thanks to chappy for the above SELECT statement.
]]>