About a year ago, I wrote blog post about SQL SERVER – 2005 – Last Ran Query – Recently Ran Query. Since, then I have received many question regarding how this is better than fn_get_sql() or DBCC INPUTBUFFER.
The Short Answer in is both of them will be deprecated.
Please refer to following update query to [...]
Archive for the ‘SQL System Table’ Category
SQL SERVER – Recently Executed T-SQL Query
Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology, tagged SQL DMV on October 16, 2009 | Leave a Comment »
SQL SERVER – Get Query Plan Along with Query Text and Execution Count
Posted in Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology on August 21, 2009 | 5 Comments »
Quite often, we need to know how many any particular objects have been executed on our server and what their execution plan is. I use the following handy script, which I use when I need to know the details regarding how many times any query has ran on my server along with its execution plan. [...]
SQL SERVER – Measure CPU Pressure – CPU Business
Posted in Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology on August 17, 2009 | 2 Comments »
Many a time, DBAs face the following question: can the CPU handle the current transaction? I have seen many DBAs getting confused on whether they should get a CPU with higher clock speed or more number of CPUs when it is time to upgrade system. As we all know, the CPU (or the Central Processing [...]
SQL SERVER – Get Last Running Query Based on SPID
Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology on July 19, 2009 | 7 Comments »
We often need to find the last running query or based on SPID need to know which query was executed. SPID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.
To know which sessions are running currently, run the following command:
SELECT @@SPID
GO
In [...]
SQL SERVER – Performance Counters from System Views – By Kevin Mckenna
Posted in Pinal Dave, Readers Contribution, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology on June 10, 2009 | 1 Comment »
I just love social media and all the new concepts of Web 2.0. There are bloggers who are overwhelmed by the new concepts of technology and are not able to keep pace with it. But I like taking such challenges. Twitter has acquired tremendous popularity nowadays and just like everybody else I am also fond [...]
SQL SERVER – List All Objects Created on All Filegroups in Database
Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology on June 1, 2009 | 4 Comments »
When I pen down any article I always keep my readers in my mind. With every topic of SQL server I cover, I try to bring readers closer to this technology. So, whenever I receive follow up questions from my readers I am exhilarated! Sometime back I had covered a topic – SQL SERVER – [...]
SQL SERVER – List All the Tables for All Databases Using System Tables
Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology, tagged undocumented SQL on April 26, 2009 | 7 Comments »
Today we will go over very simple script which will list all the tables for all the database.
sp_msforeachdb ’select ”?” AS db, * from [?].sys.tables’
Update: Based on comments received below I have updated this article. Thank you to all the readers. This is good example where something small like this have good participation from readers.
Reference : Pinal Dave (http://www.SQLAuthority.com)
SQL SERVER – Interesting Observation of DMV of Active Transactions and DMV of Current Transactions
Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology, tagged SQL DMV, SQL Transactions on April 11, 2009 | 2 Comments »
This post is about a riveting observation I made a few days back. While playing with transactions I came across two DMVs that are associated with Transactions.
1) sys.dm_tran_active_transactions – Returns information about transactions for the instance of SQL Server.
2) sys.dm_tran_current_transaction – Returns a single row that displays the state information of the transaction in the [...]
SQL SERVER – Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column
Posted in Pinal Dave, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology, tagged Identity on April 1, 2009 | 16 Comments »
Some time ago I was helping one of my Junior Developers who presented me with an interesting situation. He had a table with Identity Column. Because of some reasons he was compelled to delete few rows from the table. On inserting new rows in the table he noticed that the rows started from the [...]
SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization
Posted in Best Practices, Pinal Dave, SQL, SQL Authority, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table, SQL Tips and Tricks, T SQL, Technology on March 21, 2009 | 9 Comments »
Every batch (T-SQL, SP etc) when ran creates execution plan which is stored in system for re-use. Due to this reason large number of query plans are stored in system. However, there are plenty of plans which are only used once and have never re-used again. One time ran batch plans wastes memory and resources.
SQL [...]
About Pinal Dave
Pinalkumar Dave is a Microsoft SQL Server MVP and a Mentor for Solid Quality India. He has written over 1100 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect, Corporate Trainer and Project Manager, who specializes in SQL Server Programming and has 7 years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCDBA and MCAD (.NET). He was awarded Regional Mentor for PASS Asia.
-
Blog Stats
- 10,987,074 Readers
SQLAuthority Links

My Homepage
My Resume
My Other Blog
--------------------
Top Downloads
PDF Downloads
Script Downloads
Script Bank
Favorite Scripts
All Scripts - 1
All Scripts - 2
Top Articles
Best Articles
Favorite Articles - 1
Favorite Articles - 2
--------------------
SQL Interview Q & A
SQL Coding Standards
SQL FAQ Download
--------------------
Jobs @ SQLAuthority
Top 7 Commenters
365 - Imran Mohammed
138- Brian Tkatch
57 - Tejas Shah
56 - Jacob Sebastian
47 - Jerry Hung
46 - Kuldip Bhatt
43 - Ashish GilhotraCategories
- About Me (61)
- Best Practices (91)
- Business Intelligence (14)
- Data Warehousing (30)
- Database (269)
- DBA (125)
- MVP (98)
- Poll (5)
- Readers Contribution (37)
- Readers Question (45)
- Software Development (67)
- SQL Add-On (91)
- SQL Backup and Restore (54)
- SQL BOL (10)
- SQL Coding Standards (20)
- SQL Constraint and Keys (51)
- SQL Cursor (28)
- SQL Data Storage (45)
- SQL DateTime (37)
- SQL Documentation (219)
- SQL Download (222)
- SQL Error Messages (125)
- SQL Function (124)
- SQL Humor (25)
- SQL Index (98)
- SQL Interview Questions and Answers (58)
- SQL Joins (64)
- SQL Optimization (66)
- SQL Performance (223)
- SQL Puzzle (26)
- SQL Security (119)
- SQL Server DBCC (42)
- SQL Server Management Studio (28)
- SQL Stored Procedure (102)
- SQL String (19)
- SQL System Table (50)
- SQL Trigger (24)
- SQL User Group (48)
- SQL Utility (126)
- SQL White Papers (37)
- SQLAuthority (375)
- SQL Training (1)
- SQLAuthority Author Visit (80)
- SQLAuthority Book Review (19)
- SQLAuthority News (345)
- SQLAuthority Website Review (29)
- SQLServer (77)
- Tech (915)
- Pinal Dave (905)
- SQL Scripts (533)
- Technology (1158)
- SQL (1158)
- SQL Authority (1158)
- SQL Query (1158)
- SQL Server (1158)
- SQL Tips and Tricks (1158)
- T SQL (1158)
-
Top Posts
- SQL SERVER - Insert Data From One Table to Another Table - INSERT INTO SELECT - SELECT INTO TABLE
- SQL SERVER - Insert Multiple Records Using One Insert Statement - Use of UNION ALL
- SQL SERVER - Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
- SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server
- SQL Server Interview Questions and Answers Complete List Download
- SQL SERVER - Convert Text to Numbers (Integer) - CAST and CONVERT
- SQL SERVER - 2005 - Create Script to Copy Database Schema and All The Objects - Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
- SQL SERVER - Shrinking Truncate Log File - Log Full
- SQL SERVER - 2005 List All Tables of Database
- SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
- SQL SERVER - Restore Database Backup using SQL Script (T-SQL)
- SQL Server Interview Questions and Answers - Part 1
-
Authors
-
pinaldave
- SQLAuthority News – Book Review – Expert SQL Server 2008 Encryption by Michael Coles
- SQL SERVER – Understanding Table Hints with Examples
- SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table
- SQL SERVER – 2005 2008 – Backup, Integrity Check and Index Optimization By Ola Hallengren
- SQLAuthority News – Notes of Excellent Experience at SQL PASS 2009 Summit, Seattle
- SQL SERVER – Whitepaper Consolidation Using SQL Server 2008
- SQLAuthority News – Disk Partition Alignment Best Practices for SQL Server
- SQL SERVER – Policy Based Management – Create, Evaluate and Fix Policies
- SQL SERVER – Disable CHECK Constraint – Enable CHECK Constraint
- SQL SERVER – Sharepoint Resource Available for SQL Server
-
Archives
- November 2009
- October 2009
- September 2009
- August 2009
- July 2009
- June 2009
- May 2009
- April 2009
- March 2009
- February 2009
- January 2009
- December 2008
- November 2008
- October 2008
- September 2008
- August 2008
- July 2008
- June 2008
- May 2008
- April 2008
- March 2008
- February 2008
- January 2008
- December 2007
- November 2007
- October 2007
- September 2007
- August 2007
- July 2007
- June 2007
- May 2007
- April 2007
- March 2007
- February 2007
- January 2007
- December 2006
- November 2006
Pages
Category Cloud
About Me Best Practices Database DBA MVP Pinal Dave Software Development SQL SQL Add-On SQL Authority SQLAuthority Author Visit SQLAuthority News SQL Documentation SQL Download SQL Error Messages SQL Function SQL Index SQL Joins SQL Optimization SQL Performance SQL Query SQL Scripts SQL Security SQLServer SQL Server SQL Stored Procedure SQL Tips and Tricks SQL Utility Technology T SQL


