Friday, March 30, 2012
If exists..
Is there any way to check whether a column is there in the table, if it is there i need to drop it through script.
i'm looking for the script, something like this..
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tbl_Product_Tbl_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tbl_Product] DROP CONSTRAINT FK_Tbl_Product_Tbl_Products
GO
In the same way i need to check for a column and drop it through script.
Any help would be greatly appreciated.
Thanks in advance.Look at the INFORMATION_SCHEMA.Columns View...
But I would advise against any auto mucking|||Hi Brett..
Here is what i did..
if exists (select * from information_schema.columns where table_name = 'tablename' and column_name = 'columnname')
alter table tablename drop column columnname
go
alter table tablename add columnname varchar(255) null
go
It worked..
Thanks.sql
Wednesday, March 28, 2012
If exists for temp table
temp table ##temptable if exist before I try to create it.
Thanks.create table ##temptable (id int)
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||I forgot the drop part, here is the whole thing
CREATE TABLE ##temptable (id int)
GO
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
DROP TABLE ##temptable
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
GO
CREATE TABLE ##temptable (id int)
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||Thanks............
"SQL" wrote:
> I forgot the drop part, here is the whole thing
> CREATE TABLE ##temptable (id int)
> GO
> IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
> BEGIN
> PRINT '##temptable exists!'
> DROP TABLE ##temptable
> END
> ELSE
> BEGIN
> PRINT '##temptable does not exist!'
> END
> GO
> CREATE TABLE ##temptable (id int)
> GO
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> DXC wrote:
> > What is the syntax to drop a temporary table if exists ? I want to drop a
> > temp table ##temptable if exist before I try to create it.
> >
> > Thanks.
>
If exists for temp table
temp table ##temptable if exist before I try to create it.
Thanks.create table ##temptable (id int)
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||I forgot the drop part, here is the whole thing
CREATE TABLE ##temptable (id int)
GO
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
DROP TABLE ##temptable
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
GO
CREATE TABLE ##temptable (id int)
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||Thanks............
"SQL" wrote:
> I forgot the drop part, here is the whole thing
> CREATE TABLE ##temptable (id int)
> GO
> IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
> BEGIN
> PRINT '##temptable exists!'
> DROP TABLE ##temptable
> END
> ELSE
> BEGIN
> PRINT '##temptable does not exist!'
> END
> GO
> CREATE TABLE ##temptable (id int)
> GO
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> DXC wrote:
>
IF EXISTS for drop table.
temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
Message posted via http://www.droptable.com
if object_id('tempdb..#temp_item') is not null
drop table #temp_item
AMB
"Grant H via droptable.com" wrote:
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com
>
|||A sample for the employee table in the Northwind database:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees]
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Grant H via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:1b883ba01f1b4a5481ba1fbd07890119@.droptable.co m...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com
|||Use Alejandro's example for temp tables, and Jens' example for permanent
tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Grant H via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:1b883ba01f1b4a5481ba1fbd07890119@.droptable.co m...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com
IF EXISTS for drop table.
temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
Message posted via http://www.droptable.comif object_id('tempdb..#temp_item') is not null
drop table #temp_item
AMB
"Grant H via droptable.com" wrote:
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com
>|||A sample for the employee table in the Northwind database:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable
') = 1)
drop table [dbo].[Employees]
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Grant H via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:1b883ba01f1b4a5481ba1fbd07890119@.SQ
droptable.com...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com|||Use Alejandro's example for temp tables, and Jens' example for permanent
tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Grant H via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:1b883ba01f1b4a5481ba1fbd07890119@.SQ
droptable.com...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com
IF EXISTS for drop table.
temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
--
Message posted via http://www.sqlmonster.comif object_id('tempdb..#temp_item') is not null
drop table #temp_item
AMB
"Grant H via SQLMonster.com" wrote:
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.sqlmonster.com
>|||A sample for the employee table in the Northwind database:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees]
GO
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Grant H via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:1b883ba01f1b4a5481ba1fbd07890119@.SQLMonster.com...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.sqlmonster.com|||Use Alejandro's example for temp tables, and Jens' example for permanent
tables.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Grant H via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:1b883ba01f1b4a5481ba1fbd07890119@.SQLMonster.com...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.sqlmonster.comsql
if exists DROP PROCEDURE/VIEW
for a table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[myTABLE]
and for a procedure
IF EXISTS ?
DROP PROCEDURE [dbo].[SP_myTABLE_Count]
thank you for helpingPlease excuse me, but I find the number of people who can't use google abolutely unbelievable. A search for sys.objects gives the answer, and the type is P for TSQL stored procedures.
However, keep in mind that SQL Server 2005 hides objects on which you have no access, so even if you query sys.objects first you may get an error, and you should handle that situation.|||please excuse me, but a forum is not google|||please excuse me, but a forum is not google
I am perfectly aware of that. What I tried to point out is that very simple questions, like this one, can very easily be solved by searching google. And it's even way faster. Even better, by eliminating a few of the simpler questions, the users of the forum can use their time trying to resolve a bit tougher issues, for which there is NOT an obvious solution when searching google. I do think you get my point.
By all means, do not think of my post as a personal critism, rather a good guideline for the future. My approach is alway as this: Try to solve, search with google, post on forum.|||actually u need not think that hard to get the "if exists" script. sql server can do it for u. right click on any SP from EM and select "generate SQL Script" or from QA Object-Browser select "create drop script" by right clicking on a SP. check the code generated.....
If EXISITS.....DROP Table doesn't work
Can any of you tell me what I've done wrong in the statement below. The
problem is that when I run the whole statement, it won't drop the table if
it already exist. If I then just run the first 2-3 lines where it looks for
the table and then drop it if it exists, it seems to work.
It might just be me that haven't got the syntax right, but it puzzles me why
it seems to behave differently depending on how much of the statement I run.
The way I have noticed the problem, is that I'm using the same code for 2
databases, where I use 2 different columns in one of the tables. In one
table I use the columns "navn1' and "navn2" where in the other I use
"kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
(and where the temp table doesn't exist already) it works fine and do the
job. Then I change the names of those 2 columns to match the names in the
second database -run the code, and then I get an "Invalid column name
"kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
using the same "version" of the temp table as I created with the first run
of the code.
When I then just execute the first lines of my code where it checks for the
table and drop it if it exist, and then afterwards run the full script, then
it works fine with the new names.
To me that looks like it wont drop the table if it exist, but I might be
wrong?
Additionally I've noticed that temp tables are being named slightly
different if they are local or global temp tables. In this case I'm using a
global temp table and when I run the "SELECT....from
INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
expected. If I use a local temp table it returns the value
"#ejd_adm_temp_____________..." which means that I can't find it in
INFORMATION_SCHEMA.TABLES because it has a different name.
Is this a known issue or is it just me that are missing something on how to
use temp tables (...could very well be the case..:-)...).
Here's the code that I'm running...
USE TESTDE
IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '##ejd_adm_temp')
DROP table ##ejd_adm_temp
select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
, a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
INTO ##ejd_adm_temp
FROM lejer l
JOIN ejendom e on l.ejendomnr=e.ejendomnr
JOIN AdrFunk AF on e.ejendomID=AF.recordID
JOIN Adresse A on A.adresseident=AF.adresseident
Regards
Steen
Use
IF OBJECT_ID('##ejd_adm_temp') IS NOT NULL
For more info see http://www.aspfaq.com/2458
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>
|||Use LIKE instead of = with the table name. Temporary tables can and do
have strange names. Anyway, why are you using global temp tables and why
are you trying to drop it.
Adrian
Steen Persson wrote:
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me why
> it seems to behave differently depending on how much of the statement I run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for the
> table and drop it if it exist, and then afterwards run the full script, then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>
|||temp tables are stored in tempdb. The technique is the same regardless of
whether the temp table is a regular temp table or a global temp table.
set nocount on
create table #emp (emp_id int)
create table ##emp (emp_id int)
select object_id('tempdb..#emp')
select object_id('tempdb..##emp')
if object_id('tempdb..#emp') is not null
drop table #emp
if object_id('tempdb..##emp') is not null
drop table ##emp
if object_id('tempdb..#emp') is not null
drop table #emp
else
print '#emp does not exist'
if object_id('tempdb..##emp') is not null
drop table ##emp
else
print '##emp does not exist'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>
|||Hi Adrian
Maybe there's no real reason to drop it. My original reason for dropping the
table, was because I wanted to run this code for both databases/tables in
the same script. Since I then had slightly different column names in the
tables, I had to drop the table in between. I've later changed my mind
though, so I think I'll run it in two seperate jobs and then I don't need to
drop the table.
Also there where no reason for using a global temp table, but I tried it as
a part of my testing where I had problems in dropping the table if it was
already there. Since I could see that my local temp table was named
differently than a global temp table, I tried with the global temp table.
I can easily get around the issue in this case, but I was just puzzled why
it didn't seemed to drop the table when running the whole script when it can
do it if I just run that part of the script. I'm still a learner in SQL , so
I'd like to find out why it behaves like it does - maybe next time I really
need to get it working...:-).
BTW: I also tried using LIKE instead of =, but that gave me the same
problem.
I'll work a bit more with it and also try Aaron's suggestion...
Regards
Steen
Adrian Edwards wrote:[vbcol=seagreen]
> Use LIKE instead of = with the table name. Temporary tables can and do
> have strange names. Anyway, why are you using global temp tables and
> why are you trying to drop it.
> Adrian
> Steen Persson wrote:
If EXISITS.....DROP Table doesn't work
Can any of you tell me what I've done wrong in the statement below. The
problem is that when I run the whole statement, it won't drop the table if
it already exist. If I then just run the first 2-3 lines where it looks for
the table and then drop it if it exists, it seems to work.
It might just be me that haven't got the syntax right, but it puzzles me why
it seems to behave differently depending on how much of the statement I run.
The way I have noticed the problem, is that I'm using the same code for 2
databases, where I use 2 different columns in one of the tables. In one
table I use the columns "navn1' and "navn2" where in the other I use
"kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
(and where the temp table doesn't exist already) it works fine and do the
job. Then I change the names of those 2 columns to match the names in the
second database -run the code, and then I get an "Invalid column name
"kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
using the same "version" of the temp table as I created with the first run
of the code.
When I then just execute the first lines of my code where it checks for the
table and drop it if it exist, and then afterwards run the full script, then
it works fine with the new names.
To me that looks like it wont drop the table if it exist, but I might be
wrong?
Additionally I've noticed that temp tables are being named slightly
different if they are local or global temp tables. In this case I'm using a
global temp table and when I run the "SELECT....from
INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
expected. If I use a local temp table it returns the value
"#ejd_adm_temp_____________..." which means that I can't find it in
INFORMATION_SCHEMA.TABLES because it has a different name.
Is this a known issue or is it just me that are missing something on how to
use temp tables (...could very well be the case..:-)...).
Here's the code that I'm running...
--
USE TESTDE
IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '##ejd_adm_temp')
DROP table ##ejd_adm_temp
select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
, a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
INTO ##ejd_adm_temp
FROM lejer l
JOIN ejendom e on l.ejendomnr=e.ejendomnr
JOIN AdrFunk AF on e.ejendomID=AF.recordID
JOIN Adresse A on A.adresseident=AF.adresseident
--
Regards
SteenUse
IF OBJECT_ID('##ejd_adm_temp') IS NOT NULL
For more info see http://www.aspfaq.com/2458
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||Use LIKE instead of = with the table name. Temporary tables can and do
have strange names. Anyway, why are you using global temp tables and why
are you trying to drop it.
Adrian
Steen Persson wrote:
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me why
> it seems to behave differently depending on how much of the statement I run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for the
> table and drop it if it exist, and then afterwards run the full script, then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||temp tables are stored in tempdb. The technique is the same regardless of
whether the temp table is a regular temp table or a global temp table.
set nocount on
create table #emp (emp_id int)
create table ##emp (emp_id int)
select object_id('tempdb..#emp')
select object_id('tempdb..##emp')
if object_id('tempdb..#emp') is not null
drop table #emp
if object_id('tempdb..##emp') is not null
drop table ##emp
if object_id('tempdb..#emp') is not null
drop table #emp
else
print '#emp does not exist'
if object_id('tempdb..##emp') is not null
drop table ##emp
else
print '##emp does not exist'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||Hi Adrian
Maybe there's no real reason to drop it. My original reason for dropping the
table, was because I wanted to run this code for both databases/tables in
the same script. Since I then had slightly different column names in the
tables, I had to drop the table in between. I've later changed my mind
though, so I think I'll run it in two seperate jobs and then I don't need to
drop the table.
Also there where no reason for using a global temp table, but I tried it as
a part of my testing where I had problems in dropping the table if it was
already there. Since I could see that my local temp table was named
differently than a global temp table, I tried with the global temp table.
I can easily get around the issue in this case, but I was just puzzled why
it didn't seemed to drop the table when running the whole script when it can
do it if I just run that part of the script. I'm still a learner in SQL , so
I'd like to find out why it behaves like it does - maybe next time I really
need to get it working...:-).
BTW: I also tried using LIKE instead of =, but that gave me the same
problem.
I'll work a bit more with it and also try Aaron's suggestion...
Regards
Steen
Adrian Edwards wrote:
> Use LIKE instead of = with the table name. Temporary tables can and do
> have strange names. Anyway, why are you using global temp tables and
> why are you trying to drop it.
> Adrian
> Steen Persson wrote:
>> Hi
>> Can any of you tell me what I've done wrong in the statement below.
>> The problem is that when I run the whole statement, it won't drop
>> the table if it already exist. If I then just run the first 2-3
>> lines where it looks for the table and then drop it if it exists, it
>> seems to work.
>> It might just be me that haven't got the syntax right, but it
>> puzzles me why it seems to behave differently depending on how much
>> of the statement I run. The way I have noticed the problem, is that
>> I'm using the same code for 2 databases, where I use 2 different
>> columns in one of the tables. In one table I use the columns "navn1'
>> and "navn2" where in the other I use "kontaktnavn1" and
>> "kontaktnavn2". If I then run my code on the first base (and where
>> the temp table doesn't exist already) it works fine and do the job.
>> Then I change the names of those 2 columns to match the names in the
>> second database -run the code, and then I get an "Invalid column
>> name "kontaktnavn1 and "kontaktnavn2. To me that looks like the code
>> it still using the same "version" of the temp table as I created
>> with the first run of the code.
>> When I then just execute the first lines of my code where it checks
>> for the table and drop it if it exist, and then afterwards run the
>> full script, then it works fine with the new names.
>> To me that looks like it wont drop the table if it exist, but I
>> might be wrong?
>> Additionally I've noticed that temp tables are being named slightly
>> different if they are local or global temp tables. In this case I'm
>> using a global temp table and when I run the "SELECT....from
>> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
>> expected. If I use a local temp table it returns the value
>> "#ejd_adm_temp_____________..." which means that I can't find it in
>> INFORMATION_SCHEMA.TABLES because it has a different name.
>> Is this a known issue or is it just me that are missing something on
>> how to use temp tables (...could very well be the case..:-)...).
>>
>> Here's the code that I'm running...
>> --
>> USE TESTDE
>> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
>> WHERE TABLE_NAME = '##ejd_adm_temp')
>> DROP table ##ejd_adm_temp
>> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
>> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
>> l.opkrbetalmeddel INTO ##ejd_adm_temp
>> FROM lejer l
>> JOIN ejendom e on l.ejendomnr=e.ejendomnr
>> JOIN AdrFunk AF on e.ejendomID=AF.recordID
>> JOIN Adresse A on A.adresseident=AF.adresseident
>> --
>> Regards
>> Steensql
Monday, March 26, 2012
IE7 + ReportViewer: cuts off export and zoom drop downs
Friday, March 9, 2012
Identity constraint - Drop Create dynamically?
Greetings all,
I have an identity field that I need to drop the constraint and recreate it inside a stored procedure. Actually if there is a better way to do what I'm trying to do, I'm all ears.
My concern is that the 2^31 will eventually be exceeded on the int declaration for this field and I'm not using the field for anything other than to identify uniqueness for a specific task.
What I would like to do is re-number this field each time the table is populated. (not remember the last index)Once I'm finished with the table, I'd like the constraint to be removed.
Any thoughts?
Adamus
The only 'real' option is to drop the existing IDENTITY field, and recreate a new one.|||I was leaning that way.
Thank you,
Adamus
Wednesday, March 7, 2012
Identity Columns
started dumping of data into it.
After that i want to drop the identity constraint on that column using t-sql script.
is it possible? if so how can i do it.
Nope, you cannot remove the identity property from a column. What you can do is to transfer the values to a different column and drop the column:
create table testIdentity
(
identityColumn int identity
)
go
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
go
alter table testIdentity
add nonIdentityColumn int null
go
update testIdentity
set nonIdentityColumn = identityColumn
go
alter table testIdentity
drop column identityColumn
go
select *
from testIdentity
But i would like to know why it is not possible through t-sql while we are able to do it from EM|||
Because EM does something along the lines of what I did. EM actually is pretty inefficient in many cases in how it performs operations (like adding a new column it does a drop column then and adds a new column where an ALTER TABLE would suffice). But it is usually understandable as it takes the easiest, most straightforward path for automation, rather than a method that looks better but cannot be automated quite as easy.
One thing to try is to trace what EM does using profiler. That is a great place to see the queries it does. In Management Studio, I changed the identity property, and these are the action queries that were sent:
CREATE TABLE dbo.Tmp_testIdentity
(
IdentityColumn int NOT NULL
) ON [PRIMARY]
go
IF EXISTS(SELECT * FROM dbo.testIdentity)
EXEC('INSERT INTO dbo.Tmp_testIdentity (IdentityColumn)
SELECT IdentityColumn FROM dbo.testIdentity WITH (HOLDLOCK TABLOCKX)')
go
DROP TABLE dbo.testIdentity
go
EXECUTE sp_rename N'dbo.Tmp_testIdentity', N'testIdentity', 'OBJECT'
go
The method I gave you is far less distructive, but either will work.
Friday, February 24, 2012
Identity Column
identity property of the column throug Alter Table Alter column statement.
Can anybody tell How?
Thanking you
Toeen> I have created an identity column in a table. Now I want to drop the
> identity property of the column throug Alter Table Alter column statement.
> Can anybody tell How?
You can't. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Identity Column
identity property of the column throug Alter Table Alter column statement.
Can anybody tell How?
Thanking you
Toeen> I have created an identity column in a table. Now I want to drop the
> identity property of the column throug Alter Table Alter column statement.
> Can anybody tell How?
You can't. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.