SQL SERVER – Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database | Journey to SQL Authority with Pinal Dave

UPDATE : SQL SERVER - 2005 - Find Tables With Foreign Key Constraint in Database This is very long query. Optionally, we can limit the query to return results for one or more than one table. SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN… Continue Reading...

Source: SQL SERVER – Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Query to Find ByteSize of All the Tables in Database | Journey to SQL Authority with Pinal Dave

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 (http://blog.SQLAuthority.com) Continue Reading...

Source: SQL SERVER – Query to Find ByteSize of All the Tables in Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database | Journey to SQL Authority with Pinal Dave

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 and auto generate the script… Continue Reading...

Source: SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created | Journey to SQL Authority with Pinal Dave

Following cursor query runs through 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 opertation on that table like delete, print or reindex. SET NOCOUNT ON DECLARE @lcl_name… Continue Reading...

Source: SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created | Journey to SQL Authority with Pinal Dave

SQL SERVER – Cursor to Kill All Process in Database | Journey to SQL Authority with Pinal Dave

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 INT DECLARE @tString VARCHAR(15) DECLARE… Continue Reading...

Source: SQL SERVER – Cursor to Kill All Process in Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure | Journey to SQL Authority with Pinal Dave

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 ON c.id=o.id WHERE c.TEXT LIKE… Continue Reading...

Source: SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure | Journey to SQL Authority with Pinal Dave

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. | Journey to SQL Authority with Pinal Dave

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 an MSX server. The job… Continue Reading...

Source: 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. | Journey to SQL Authority with Pinal Dave

SQL SERVER – Shrinking Truncate Log File – Log Full | Journey to SQL Authority with Pinal Dave

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(, 1) BACKUP LOG WITH TRUNCATE_ONLY DBCC SHRINKFILE(, 1) GO [Update:… Continue Reading...

Source: SQL SERVER – Shrinking Truncate Log File – Log Full | Journey to SQL Authority with Pinal Dave

SQL SERVER – Simple Example of Cursor | Journey to SQL Authority with Pinal Dave

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 CURSOR SET @getAccountID = CURSOR FOR SELECT… Continue Reading...

Source: SQL SERVER – Simple Example of Cursor | Journey to SQL Authority with Pinal Dave

SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database | Journey to SQL Authority with Pinal Dave

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 database_default GROUP BY a.table_name, a.row_count, a.data_size ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC DROP TABLE #temp… Continue Reading...

Source: SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database | Journey to SQL Authority with Pinal Dave

SQL SERVER – Query Analyzer Shortcuts | Journey to SQL Authority with Pinal Dave

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 Display results in grid format… Continue Reading...

Source: SQL SERVER – Query Analyzer Shortcuts | Journey to SQL Authority with Pinal Dave

SQL SERVER – CASE Statement/Expression Examples and Explanation | Journey to SQL Authority with Pinal Dave

CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions. Two basic formulations for CASE expression 1) Simple CASE expressions A simple CASE expression… Continue Reading...

Source: SQL SERVER – CASE Statement/Expression Examples and Explanation | Journey to SQL Authority with Pinal Dave

SQL SERVER – 64 bit Architecture and White Paper | Journey to SQL Authority with Pinal Dave

In supportability, manageability, scalability, performance, interoperability, and business intelligence, SQL Server 2005 provides far richer 64-bit support than its predecessor. This paper describes these enhancements. Read the original paper here. Following abstract is taken from the same paper. Another interesting article on 64-bit Computing with SQL Server 2005 is here. The primary differences between the… Continue Reading...

Source: SQL SERVER – 64 bit Architecture and White Paper | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Introduction | Journey to SQL Authority with Pinal Dave

UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. Hello All, Thank you all for sending request for Interview Questions and Answers. It begins from today. This is six part series. I will update this post once… Continue Reading...

Source: SQL Server Interview Questions and Answers – Introduction | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 1 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What is RDBMS? Relational Data Base Management Systems (RDBMS) are database management systems that… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 1 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 2 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What is the difference between clustered and a non-clustered index? A clustered index is… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 2 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 3 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What is a NOLOCK? Using the NOLOCK query optimiser hint is generally considered good… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 3 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 4 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What is SQL Profiler? SQL Profiler is a graphical tool that allows system administrators… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 4 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 5 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What command do we use to rename a db? sp_renamedb ‘oldname’ , ‘newname’ If… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 5 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers – Part 6 | Journey to SQL Authority with Pinal Dave

SQL Server Interview Questions and Answers Print Book Available (207 Pages) | Sample Chapters UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers. What are the properties of the Relational tables? Relational tables have six properties: Values… Continue Reading...

Source: SQL Server Interview Questions and Answers – Part 6 | Journey to SQL Authority with Pinal Dave