Posted in Author Pinal, SQL, SQL Authority, SQL Cursor, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 20, 2008 | 4 Comments »
SQLAuthority Blog reader YordanGeorgiev has submitted very interesting SP, which uses cursor to generate text of all the Stored Procedure of current Database. This task can be done many ways, however, this is also interesting method.
USE AdventureWorks
GO
DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT s.name
FROM sysobjects s
WHERE type = ‘P’
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = [...]
Read Full Post »
Posted in Author Pinal, Best Practices, DBA, Data Warehousing, Database, SQL, SQL Authority, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL Data Storage, SQL Documentation, SQL Download, SQL Function, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, SQLAuthority, T SQL, Technology on September 25, 2008 | 3 Comments »
SQL SERVER - Guidelines and Coding Standards complete List Download
Coding standards and guidelines are very important for any developer on the path of successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where [...]
Read Full Post »
Posted in Author Pinal, Database, SQL, SQL Authority, SQL Constraint and Keys, SQL Cursor, SQL Data Storage, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, SQL XML, SQLAuthority, T SQL, Technology on September 20, 2008 | 12 Comments »
Download SQL Server 2008 Interview Questions and Answers Complete List
Interview is very important event for any person. A good interview leads to good career if candidate is willing to learn. I always enjoy interview questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server [...]
Read Full Post »
Posted in Author Pinal, Data Warehousing, SQL, SQL Authority, SQL Constraint and Keys, SQL Cursor, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQLAuthority, T SQL, Technology on September 13, 2008 | 9 Comments »
SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
1) General Questions of SQL SERVER
What is Cursor?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In [...]
Read Full Post »
eaders are very important to me. Without their active participation this site would not be the community helping web site. I encourage readers participation and request that you help other users with your knowledge.
I recently come across very good communication between two of blog readers. I want to thank you Imran Mohammed for taking time [...]
Read Full Post »
Posted in Author Pinal, Best Practices, Database, SQL, SQL Authority, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL Function, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, T SQL, Technology on May 21, 2008 | 12 Comments »
I recently came across very nice article about optimization tips for SQL Server 2005. Here is the list of those 12 tips.
Twelve Tips For Optimizing Sql Server 2005 Query Performance
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old [...]
Read Full Post »
It is commonly believed that cursor are Database Objects. I have always given the definition of cursor as SQL Server cursors are database objects used to manipulate data in a set on a row-by-row basis.
Just a few days ago - Imran one of the active reader of blog asked me question if cursor is database [...]
Read Full Post »
Posted in About Me, Author Pinal, Best Practices, DBA, Data Warehousing, Database, SQL, SQL Add-On, SQL Authority, SQL Backup and Restore, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL Data Storage, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Humor, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, SQLAuthority, SQLAuthority Author Visit, SQLAuthority Book Review, SQLAuthority News, SQLAuthority Website Review, Software Development, T SQL, Technology on March 7, 2008 | 9 Comments »
I am very pleased to write my 500th post. After 500 posts, I still have same feeling when I wrote first post on this blog. I would like to thank my family for their continuous support in writing this blog. Most of all I want to thank all of YOU for being wonderful readers of [...]
Read Full Post »
Posted in Author Pinal, SQL, SQL Authority, SQL Cursor, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on March 5, 2008 | 17 Comments »
I have recently received email that I should update SQL SERVER - Simple Example of Cursor with example of AdventureWorks database.
Simple Example of Cursor using AdventureWorks Database is listed here.
USE AdventureWorks
GO
DECLARE @ProductID INT
DECLARE @getProductID CURSOR
SET @getProductID = CURSOR FOR
[...]
Read Full Post »
Posted in Author Pinal, DBA, Data Warehousing, Database, SQL, SQL Add-On, SQL Authority, SQL Backup and Restore, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Humor, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, Software Development, T SQL, Technology on November 4, 2007 | 2 Comments »
SQL SERVER - Cursor to Kill All Process in Database
SQL SERVER - Find Stored Procedure Related to Table in Database - Search in All Stored procedure
SQL SERVER - Shrinking Truncate Log File - Log Full
SQL SERVER - Simple Example of Cursor
SQL SERVER - UDF - Function to Convert Text String to Title Case - Proper [...]
Read Full Post »
Posted in Author Pinal, SQL, SQL Authority, SQL Cursor, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on October 6, 2007 | 14 Comments »
I was going through comments on various posts to see if I have missed to answer any comments. I realized that there are quite a few times I have answered question which discuss about how to call stored procedure or query on linked server or another server. This is very detailed topic, I will [...]
Read Full Post »
Posted in Author Pinal, SQL, SQL Authority, SQL Cursor, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on September 23, 2007 | 5 Comments »
Jr. DBA in my company only have access to the database which they need to use. Often they try to access database and if they do not have permission they face error. Jr. DBAs always check which database they have access using following system stored procedure. It is very reliable and provides accurate information.
Sytanx:
EXEC sp_MShasdbaccess
GO
ResultSet: ( [...]
Read Full Post »
Posted in Author Pinal, SQL, SQL Authority, SQL Coding Standards, SQL Cursor, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on September 22, 2007 | No Comments »
I was glad when I discovered this Extended Stored Procedure myself. I always used different syntax to retrieve server information. Many of information I was looking up using system information of the windows operating system.
Syntax:
EXEC xp_msver
ResultSet:
Index Name [...]
Read Full Post »
Posted in Author Pinal, DBA, SQL, SQL Authority, SQL Cursor, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on September 13, 2007 | 7 Comments »
What is the difference between EXEC and EXECUTE?
They are the same. Both of them executes stored procedure when called as
EXEC sp_help
GO
EXECUTE sp_help
GO
I have seen enough times developer getting confused between EXEC and EXEC(). EXEC command executes stored procedure where as EXEC() function takes dynamic string as input and executes them.
EXEC(’EXEC sp_help’)
GO
Another common mistakes I have seen is not [...]
Read Full Post »
Posted in Author Pinal, SQL, SQL Authority, SQL Cursor, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on September 3, 2007 | 14 Comments »
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 this SQL SERVER - Find Stored Procedure Related to [...]
Read Full Post »
Posted in Author Pinal, Database, SQL, SQL Authority, SQL Backup and Restore, SQL Constraint and Keys, SQL Cursor, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQLAuthority Book Review, Software Development, T SQL, Technology on August 21, 2007 | 200 Comments »
Following quick tutorial demonstrates how to create T-SQL script to copy complete database schema and all of its objects such as Stored Procedure, Functions, Triggers, Tables, Views, Constraints etc. You can review your schema, backup for reference or use it to compare with previous backup.
Step 1 : Start
Step 2 : Welcome Screen
Step 3 : [...]
Read Full Post »
Posted in DBA, SQL, SQL Add-On, SQL Authority, SQL Backup and Restore, SQL Cursor, SQL Download, SQL Error Messages, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQLAuthority Author Visit, SQLAuthority Book Review, Software Development, T SQL, Technology on August 13, 2007 | 9 Comments »
Last weekend I visited New York City (NY) and Edison (NJ) to attend database architecture meeting with a big environmental technology firm. It was very interesting to meet CEO and few of the lead database administrators. Lots of database related things were discussed.
I will list few of the points discussed in the meeting here, due [...]
Read Full Post »
Posted in Author Pinal, SQL, SQL Authority, SQL Cursor, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on August 10, 2007 | 24 Comments »
This post is second part of my previous post about SQL SERVER - 2005 - List All Stored Procedure Modified in Last N Days
This post demonstrates the script which displays create date and modify date for any specific stored procedure in SQL Server.
USE AdventureWorks;
GO
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = ’P'
AND name = ’uspUpdateEmployeeHireInfo’
GO
Reference : Pinal Dave (http://www.SQLAuthority.com)
Read Full Post »
Posted in Author Pinal, SQL, SQL Authority, SQL Backup and Restore, SQL Cursor, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, T SQL, Technology on July 24, 2007 | 10 Comments »
I was asked following questions when discussing security issues in meeting with off-shore team of large database consultancy company few days ago. I will only discuss one of the security issue was discussed accidental data modification by developers and DBAs themselves.
How to alter modification in database by system admin himself?
How to prevent accidents due to [...]
Read Full Post »
Posted in Author Pinal, SQL, SQL Authority, SQL Cursor, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on July 3, 2007 | 6 Comments »
#TempTable and @TempVariable are different things with different scope. Their purpose is different but highly overlapping. TempTables are originated for the storage and & storage & manipulation of temporal data. TempVariables are originated (SQL Server 2000 and onwards only) for returning date-sets from table-valued functions.
Common properties of #TempTable and @TempVariable
They are instantiated in tempdb.
They [...]
Read Full Post »
My Homepage
My Resume
My Other Blog
SQL Interview Q & A
SQL FAQ Download
Search SQLAuthority 
Subscribe Email Update
SQLAuthority Feed
Translate SQLAuthority
