Feeds:
Posts
Comments

Archive for the ‘SQL Stored Procedure’ Category

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

Read Full Post »

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

Read Full Post »

SQL SERVER – Enable and Disable Index Non Clustered Indexes Using T-SQL

Posted in Pinal Dave, Readers Contribution, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Utility, T SQL, Technology on February 19, 2009 | 4 Comments »

Just a day before I received wonderful T-SQL Script from SQL Server Expert Imran Mohammed. What I am really impressed from this script is additional feature of changing recovery mode when SQL Server is enabling and disabling Index. When index are enabled changing SQL Server Recovery mode to simple makes whole operation faster. This stored [...]

Read Full Post »

SQL SERVER – FIX : ERROR : Msg 5834, Level 16, State 1, Line 1 The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration

Posted in Pinal Dave, SQL, SQL Authority, SQL Error Messages, SQL Index, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on January 31, 2009 | 1 Comment »

Yesterday I came across following error while enabling fill factor for my database server, when I was trying to write article SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor.
I ran following T-SQL script and it gave me error.
sp_configure ’show advanced options’, 1
GO
RECONFIGURE
GO
sp_configure ‘fill [...]

Read Full Post »

SQL SERVER – Find Number of Rows and Disk Space Reserved – Using sp_spaceused Interesting Observation

Posted in Pinal Dave, SQL, SQL Authority, SQL Data Storage, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Utility, T SQL, Technology on January 14, 2009 | 2 Comments »

Previously I posted SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL. Today we will look into the same issue but with some additional interesting detail.
We can find the row count using another system SP sp_spaceused. This SP gives additional information regarding disk space reserved on database as [...]

Read Full Post »

SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL

Posted in Pinal Dave, SQL, SQL Authority, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on January 2, 2009 | 16 Comments »

UPDATE : Updated this query with bug fixed with one more enhancement SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2.
Recently my company owner asked me to find which query is running longest. It was very interesting that I was not able to find any T-SQL script online which [...]

Read Full Post »

SQL SERVER – Simple Use of Cursor to Print All Stored Procedures of Database Including Schema

Posted in Database, Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 22, 2008 | 4 Comments »

I love active participation from my readers. Just a day ago I wrote article about SQL SERVER – Simple Use of Cursor to Print All Stored Procedures of Database. I just received comment from Jerry Hung who have improved on previously written article of generating text of Stored Procedure.
DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT [...]

Read Full Post »

SQL SERVER – Simple Use of Cursor to Print All Stored Procedures of Database

Posted in Pinal Dave, SQL, SQL Authority, SQL Cursor, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 20, 2008 | 5 Comments »

SQLAuthority Blog reader YordanGeorgiev has submitted very interesting SP, which uses cursor to generate text of all the Stored Procedure of current Database. This task can be done many ways, however, this is also interesting method.
USE AdventureWorks
GO
DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT s.name
FROM sysobjects s
WHERE type = ‘P’
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = [...]

Read Full Post »

SQL SERVER – Delete Backup History – Cleanup Backup History

Posted in Best Practices, Database, Pinal Dave, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 11, 2008 | 4 Comments »

SQL Server stores history of all the taken backup forever. History of all the backup is stored in msdb database. Many times older history is no more required. Following Stored Procedure can be executed with parameter which takes days of history to keep. In following example 30 is passed to keep history of month.
USE msdb
GO
DECLARE [...]

Read Full Post »

SQL SERVER – Refresh Database Using T-SQL

Posted in Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 7, 2008 | 4 Comments »

Yesterday I received following questions on blog. Ashish Agarwal asked following question.
Hi Pinal,
Can we refresh a database (like we do by right clicking database node in object explorer and clicking on refresh) thru SQL Query?
If yes, can you please tell me the query?
Thanks,
Ashish Agarwal
Answer to above question is NO. It is not possible to do [...]

Read Full Post »

Older Posts »