Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
philipstreet
Starting Member
2 Posts |
 Posted - 2007-08-06 : 07:05:09
|
| Is there an SP or T-SQL that will give me a list of any tables that are pinned in memory?I need to do this on a number of SQL 2K servers used by various applications, which we are planning on migrating to 2K5...Cheers,Phil |
|
|
philipstreet
Starting Member
2 Posts |
Posted - 2007-08-06 : 09:39:43
|
| I've found the answer myself. Simply run the following on your server;SELECT TOP 100 PERCENT U.[Name] as [Owner] , T.[Name] as [Name] , T.[Id] as [Id] , crdate [Create Date] , OBJECTPROPERTY(T.[Id], 'TableIsPinned') as Pinned , SUM (CASE WHEN si.indid = 0 or si.indid = 1 THEN si.[rows] ELSE 0 END) as [Rows] , CAST((SUM (CASE WHEN si.indid = 0 or si.indid = 1 THEN si.[dpages] * 8192 ELSE 0 END) / 1048576) as Numeric (18,2)) as [DataMB] FROM (SELECT [name], [id], uid, crdate, refdate, schema_ver FROM master..sysobjects WHERE xtype = 'U' ) T -- All User tables. INNER JOIN master..sysusers U ON T.uid = U.uid LEFT OUTER JOIN master..sysindexes SI on T.id = si.id WHERE 1=OBJECTPROPERTY(T.[Id], 'TableIsPinned') GROUP BY T.[id] , t.uid , T.[Name] , U.[Name] , T.crdate ORDER BY T.[Name] -- table name. , U.[Name] -- ownerEnjoy,Phil |
 |
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-06 : 10:08:34
|
| Just to be sure - I imagine you are running this because you know pinning is deprecated in 2005 right? |
|
|
|
|
|
|
|
|
How do I get a list of "pinned" tables?