Feeds:
Posts
Comments

Archive for December, 2013

SQL SERVER – How to filter tables in sp_MSforeachtable

Posted in Solutions, SQL SERVER, tagged , , , , on December 7, 2013| 1 Comment »

Today, I was working on customer’s test database and I wanted to delete all the tables having “_temp” in the names. Even though I could manually write the script by using a table at a time, I was looking for a shortcut. I have been using sp_MSforeachtable on test databases for multiple purposes. But as far as I know, it performs the specified operation in all tables in the particular database. So I started doing my research. First of all, I searched if there was any parameter available in the sp_MSforeachtable that we can use to filter tables objects and I found one parameter namely @whereand. Given below is the script to view the available parameters in sp_MSforeachtable.

EXEC sp_help sp_msforeachtable
--OUTPUT

sp_msforeachtable_conditional.1.1

SOLUTION :
You need to use the @whereand parameter to filter any table object in sp_MSforeachtable (Undocumented system stored procedure).
Given below is a script, where I will select table contains “_temp” in the name.

USE tempdb
GO
EXEC sp_msforeachtable
@command1 ='SELECT * FROM ?'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name like ''%_temp%'')'

Note : It is not recommended to use sp_MSforeachtable on production databases.

Read Full Post »

SQL SERVER – Fix – Error – 1011 – The correlation name ‘%.*ls’ is specified multiple times in a FROM clause.

Posted in Errors, SQL SERVER, tagged , , , , on December 6, 2013| 1 Comment »

In SQL Server, alias are very helpful, because you do not need to write the long table names again and again. I usually use ALIAS in my development to speed up the work. Yesterday, I was working on a complex query and I used a lot of alias to minimize the line of code and finally I came across an error.

Let’s discuss this in detail:
Message Number: 1011

Severity : 16

Error Message: The correlation name ‘%.*ls’ is specified multiple times in a FROM clause.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error.

Use AdventureWorks2012
GO

SELECT
p.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,p.[JobTitle]
FROM [HumanResources].[Employee] p
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]

GO

Msg 1011, Level 16, State 1, Line 2
The correlation name ‘p’ is specified multiple times in FROM clause.

Ooopps…… I am unable to execute the above qquery.

Resolution:
The resolution is very simple. Basically, you aliased more than one table with the same name, so SQL Server is unable to relate alias with table name. In the above table, I ALIASED both tables with p while I can assign one name (p) as an alias to one table only and I must change the alias of other table name (p) to something else. (That make sense also). Lets rewrite the script with the different alias and it works…….

USE [AdventureWorks2012]
GO
SELECT
e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,e.[JobTitle]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
GO
--OUTPUT

error_message_1011.1.1

Read Full Post »

SQL SERVER 2012 – How to check last modified date of Sequence Object

Posted in Solutions, SQL SERVER, tagged , , , , , on December 1, 2013| Leave a Comment »

I deployed some Sequence objects on production database and after couple of days, I was facing some problem with Sequence objects. It was little difficult to debug all sequence objects at once, so I thought of checking the sequence objects modified dates to find if I modified any Sequence object after deployment.

Given below are the two methods to find the modified dates of Sequence Object.

METHOD 1 : Using sys.sequence
Given below is the script to find the modified date of all sequence object using sys.sequences

USE AdventureWorks2012
GO
SELECT
name As [Sequence Name]
,modify_date
FROM sys.sequences
GO
--OUTPUT

Sequence modified dates

METHOD 2 : Using sys.objects
Given below is the script to find the modified date of all sequence object using sys.objects

USE AdventureWorks2012
GO
SELECT
name As [Sequence Name]
,modify_date
FROM sys.objects
WHERE [type] ='SO'
GO
--OUTPUT

Sequence modified dates

Read Full Post »

« Newer Posts