SQL Authority with Pinal Dave

Learn Now
Free Learning Videos
Upcoming Events

An Index Reduces Performance of SELECT Queries (Video)
3 Common Mistakes to Kill SQL Server Performance (Video)
SQL Server Performance Tuning Made Easy – In-Person (Video)
SQL Server Performance Tuning Made Easy – Online (Video)
3 Performance Tuning Tricks you Always Wanted to Know (Video)
Secrets of SQL Server-Database Worst Practices (Video)
Kick Start! SQL Server 2017/2019 Performance Tips and Trick (Video)

SQL SERVER - Auto Generate Script to Delete Deprecated Fields in Current Database elearning

Instant Learning ~
SQL Server Performance Tuning Practical Workshop for EVERYONE

Learn Now

Free Learning Videos

An Index Reduces Performance of SELECT Queries (Video)
3 Common Mistakes to Kill SQL Server Performance (Video)
SQL Server Performance Tuning Made Easy – In-Person (Video)
SQL Server Performance Tuning Made Easy – Online (Video)
3 Performance Tuning Tricks you Always Wanted to Know (Video)
Secrets of SQL Server-Database Worst Practices (Video)
Kick Start! SQL Server 2017/2019 Performance Tips and Trick (Video)

Upcoming Events

Instant Learning ~
SQL Server Performance Tuning Practical Workshop for EVERYONE

Jan1

SQL SERVER – Simple Example of Cursor

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Cursor, SQL Scripts

196

UPDATE: For working example using AdventureWorks visit : SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2 This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL. DECLARE @AccountID INT DECLARE @getAccountID…

Dec30

SQL SERVER – Shrinking Truncate Log File – Log Full

Pinal Dave

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

SQL Backup and Restore, SQL Log, SQL Scripts

314

UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008. Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible. USE DatabaseName GO DBCC SHRINKFILE(<TransactionLogName>, 1) BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY…

Dec20

SQL SERVER – Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL Scripts, SQL Stored Procedure

103

To fix the error which occurs after the Windows server name been changed, when trying to update or delete the jobs previously created in a SQL Server 2000 instance, or attaching msdb database. Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from…

Dec10

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure, SQL Utility

167

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results. ----Option 1 SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%' ----Option 2 SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o…

Dec1

SQL SERVER – Cursor to Kill All Process in Database

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Cursor, SQL Scripts, SQL Stored Procedure

When you run the script please make sure that you run it in different database then the one you want all the processes to be killed. CREATE TABLE #TmpWho (spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150), hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150)) INSERT INTO #TmpWho EXEC sp_who DECLARE @spid…

Nov30

SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created

Pinal Dave

SQL Tips and Tricks

SQL Cursor, SQL DateTime, SQL Scripts, SQL Server, SQL Stored Procedure

Following cursor query runs through the database and find all the table with certain prefixed (‘b_’,’delete_’). It also checks if the Table is more than certain days old or created before certain days, it will delete it. We can have any other operation on that table like to delete, print or index.

Nov20

SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure

I always mark fields to be deprecated with “dep_” as prefix. In this way, after few days, when I am sure that I do not need the field any more I run the query to auto generate the deprecation script. The script also checks for any constraint in the system…

Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 12 SQL Server database books, 30 Pluralsight courses and has written over 5000 articles on the database technology on his blog at a https://blog.sqlauthority.com. Along with 16+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.

For any SQL Server Performance Tuning Issue send an email at pinal@sqlauthority.com .

Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).

Nupur Dave is a social media enthusiast and an independent consultant.

February 33% Discount | Comprehensive Database Performance Health Check | Testimonials

SQL SERVER – Simple Example of Cursor

SQL SERVER – Shrinking Truncate Log File – Log Full

SQL SERVER – Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

SQL SERVER – Cursor to Kill All Process in Database

SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created

SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database

Exclusive Newsletter