All Articles - SQL Authority with Pinal Dave

SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

September 4, 2007

SQL Constraint and Keys, SQL Scripts, SQL System Table

My article SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key has received following question many times. I have deleted similar questions and kept only latest comment there. In SQL Server 2005 How to Find Tables With Primary Key Constraint in Database? Script…

SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

September 4, 2007

Pinal Dave

SQL Tips and Tricks

SQL Constraint and Keys, SQL Scripts, SQL Server, SQL System Table

While writing article based on my SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key I got an idea about writing this article. I was thinking if you can find primary key for any table in the database, you can sure find foreign key for any table in the database as well.

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

September 3, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Cursor, SQL Scripts, SQL Stored Procedure, SQL System Table

I receive following question many times by my team members. How can I find if particular table is being used in the stored procedure? How to search in stored procedures? How can I do dependency check for objects in stored procedure without using sp_depends? I have previously wrote article about…

SQL SERVER – Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward

September 2, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Backup and Restore, SQL Data Storage, SQL Error Messages, SQL Scripts

Following error occurs when tried to restored the differential backup. Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward Fix/WorkAround/Solution: This error happens when Full back up is not restored before attempting to restore…

SQL SERVER – 2005 – Find Database Status Using sys.databases or DATABASEPROPERTYEX

August 31, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Server DBCC, SQL System Table, SQL Utility

While writing article about database collation, I came across sys.databases and DATABASEPROPERTYEX. It was very interesting to me that this two can tell user so much about database properties. Following are main database status: (Reference: BOL Database Status) ONLINE Database is available for access. OFFLINE Database is unavailable. RESTORING One…

SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS

August 30, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Collation, SQL Scripts

This article is written based on feedback I have received on SQL SERVER – Cannot resolve collation conflict for equal to operation. Many reader asked me how to find collation of current database. There are two different ways to find out SQL Server database collation. 1) Using T-SQL (My Recommendation)…

SQL SERVER – Difference and Explanation among DECIMAL, FLOAT and NUMERIC

August 29, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Datatype, SQL Scripts

The basic difference between Decimal and Numeric : They are the exactly same. Same thing different name. The basic difference between Decimal/Numeric and Float : Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly. Decimal/Numeric is Fixed-Precision data type,…

SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan

August 28, 2007

Pinal Dave

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

DBA, Execution Plan, SQL Scripts

I was recently invited to participate in big discussion on one of the online forum, the topic was Actual Execution Plan vs. Estimated Execution Plan. I refused to participate in that particular discussion as I have very simple but strong opinion about this topic. I always use Actual Execution Plan…

SQL SERVER – 2005 – Use Always Outer Join Clause instead of (= and =)

August 27, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Deprecated Feature, SQL Joins, SQL Scripts

Yesterday I wrote about how SQL Server 2005 does not support named pipes. Today, my friend called me asking some of his query does not work. I asked him to send me the queries. I asked him to send me query. I noticed in his queries something, I have never…

SQL SERVER – 2005 – No Backup Support For Named Pipes

August 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Backup and Restore

While helping one of my DBA friend (who works in big company in LA) to upgrade SQL Server 2000 to SQL Server 2005 I just found one thing, which I have not paid attention before. SQL Server 2000 supported named pipe backup device. SQL Server 2005 does not support named…