All Articles - SQL Authority with Pinal Dave

SQL SERVER – Query Analyzer Shortcuts

January 20, 2007

Download Query Analyzer Shortcuts (PDF) Shortcut Function Shortcut Function ALT+BREAK Cancel a query CTRL+SHIFT+F2 Clear all bookmarks ALT+F1 Database object information CTRL+SHIFT+INSERT Insert a template ALT+F4 Exit CTRL+SHIFT+L Make selection lowercase CTRL+A Select all CTRL+SHIFT+M Replace template parameters CTRL+B Move the splitter CTRL+SHIFT+P Open CTRL+C Copy CTRL+SHIFT+R Remove comment CTRL+D…

SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database

January 10, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure

USE DatabaseName GO CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate…

SQL SERVER – Simple Example of Cursor

January 1, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Cursor, SQL Scripts

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…

SQL SERVER – Shrinking Truncate Log File – Log Full

December 30, 2006

Pinal Dave

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

SQL Backup and Restore, SQL Log, SQL Scripts

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…

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.

December 20, 2006

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL Scripts, SQL Stored Procedure

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…

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

December 10, 2006

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure, SQL Utility

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…

SQL SERVER – Cursor to Kill All Process in Database

December 1, 2006

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…

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

November 30, 2006

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.

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

November 20, 2006

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…

SQL SERVER – Query to Find ByteSize of All the Tables in Database

November 10, 2006

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure

SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables' ELSE ISNULL(sob.name, 'unknown') END AS Table_name, SUM(sys.length) AS Byte_Length FROM sysobjects sob, syscolumns sys WHERE sob.xtype='u' AND sys.id=sob.id GROUP BY sob.name WITH CUBE Reference : Pinal Dave (https://blog.sqlauthority.com)