Pinned Tables

Is there a way to see what tables, if any, are pinned ?

Thanks

This should be an easy task, but as far as I can tell, there is no really easy way to do this. In the following demo code, I will give an example of how to do this.

--First, the following code pins a 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 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, you need to rewrite your code to check for every table in the database, checking the OBJECTPROPERTY of each table. Who wants to volunteer to write this code?


------------------
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

Its about time I contributed something back to this forum, however minor <img src='/community/emoticons/emotion-1.gif' alt='' /><br />After messing about with various ways, this seems a nice and simple way to list all tables which are pinned. Im not sure if querying sysobjects like this is recommended, or will work in future versions.<br /><br />-- show all pinned user tables<br />select name from sysobjects where xtype = 'U' and OBJECTPROPERTY (object_id(name), 'TableIsPinned') &gt; 0<br /><br />

Cool! I wish I had thought of that.


------------------
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

For completeness sake, I just learnt how to write this code so its guaranteed not to break on future versions...

select * from INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE' and OBJECTPROPERTY(object_id(TABLE_NAME), 'TableIsPinned') > 0

...and to take a little further:

Set NoCount On

Declare @Name sysname

DECLARE CSR1 CURSOR READ_ONLY FOR
Select name from sysobjects (NoLock) where type = 'U'

OPEN CSR1

FETCH NEXT FROM CSR1
INTO@Name

WHILE @@FETCH_STATUS = 0

BEGIN
select * from INFORMATION_SCHEMA.Tables
where TABLE_TYPE = 'BASE TABLE'
and OBJECTPROPERTY(object_id(@Name), 'TableIsPinned') > 0

FETCH NEXT FROM CSR1
INTO@Name

END

CLOSE CSR1
DEALLOCATE CSR1

happy hunting,

Brett