How can I determine if a table is pinned in SQL Server memory or not?

--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.