All Articles - SQL Authority with Pinal Dave

SQL SERVER – Creating Comma Separate List From Table

February 20, 2007

SQL Function, SQL Scripts, SQL Stored Procedure, SQL String

Update : (5/5/2007) I have updated the script to support SQL SERVER 2005. Visit :SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL SERVER – FIX : Error 15023: User already exists in current database.

February 15, 2007

Pinal Dave

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

DBA, SQL Cursor, SQL Download, SQL Error Messages, SQL Joins, SQL Scripts, SQL Server DBCC, SQL Server Security, SQL Stored Procedure

Error 15023: User already exists in current database. 1) This is the best Solution. First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan. USE YourDB GO EXEC sp_change_users_login 'Report' GO Run following T-SQL Query in Query Analyzer…

SQL SERVER – Function to Convert List to Table

February 10, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL String

Update : (5/5/2007) I have updated the UDF to support SQL SERVER 2005. Visit :SQL SERVER – UDF – Function to Convert List to Table Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL SERVER – Primary Key Constraints and Unique Key Constraints

February 5, 2007

Pinal Dave

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

Database, Software Development, SQL Coding Standards, SQL Constraint and Keys, SQL Download, SQL Error Messages, SQL Index, SQL Joins, SQL Scripts, SQL Server Security

Primary Key: Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls. Create table with Primary Key: CREATE TABLE Authors ( AuthorID INT NOT NULL PRIMARY KEY, Name VARCHAR(100) NOT NULL ) GO…

SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case

February 1, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL String

Following function will convert any string to Title Case. I have this function for long time. I do not remember that if I wrote it myself or I modified from original source. Run Following T-SQL statement in query analyzer: SELECT dbo.udf_TitleCase('This function will convert this string to title case!') The…

SQL SERVER – ReIndexing Database Tables and Update Statistics on Tables

January 31, 2007

Pinal Dave

SQL Performance

Best Practices, SQL Constraint and Keys, SQL Cursor, SQL Index, SQL Joins, SQL Scripts, SQL Server DBCC, SQL Stored Procedure

SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions. Let us learn how to do ReIndexing Database Tables and Update Statistics on Tables.

SQL SERVER – Query Analyzer Short Cut to display the text of Stored Procedure

January 30, 2007

Pinal Dave

SQL Tips and Tricks

SQL Server, SQL Server Management Studio, SQL Shortcut, SQL Stored Procedure

This is quick but interesting trick to display the text of Stored Procedure in the result window. Open SQL Query Analyzer >> Tools >> Customize >> Custom Tab
type sp_helptext against Ctrl+3 (or shortcut key of your choice)

SQL SERVER – SQL Joke, SQL Humor, SQL Laugh

January 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Humor

I have heard this joke from my friend. I always wanted to write it but I was not able to find the source of the joke. This joke I have located on DavidM’s Blog on SQLTeam. It is March 1st and the first day of DBMS school The teacher starts…

SQL SERVER – Query Analyzer Shortcuts

January 20, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Download, SQL Shortcut, SQL Utility

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…