SQL System Table Archives - Page 7 of 13 - SQL Authority with Pinal Dave

SQL SERVER – Get Last Running Query Based on SPID

July 19, 2009

SQL Function, SQL Scripts, SQL Server DBCC, SQL System Table

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…

SQL SERVER – Performance Counters from System Views – By Kevin Mckenna

June 10, 2009

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL System Table

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…

SQL SERVER – List All Objects Created on All Filegroups in Database

June 1, 2009

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts, SQL System Table

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…

SQL SERVER – List All the Tables for All Databases Using System Tables

April 26, 2009

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure, SQL System Table, undocumented SQL

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…

SQL SERVER – Interesting Observation of DMV of Active Transactions and DMV of Current Transactions

April 11, 2009

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DMV, SQL Scripts, SQL System Table, SQL Transactions

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…

SQL SERVER – Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column

April 1, 2009

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Identity, SQL Index, SQL Scripts, SQL Server DBCC, SQL System Table

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…

SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization

March 21, 2009

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Best Practices, SQL Scripts, SQL Stored Procedure, SQL System Table

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…