Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>sql

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John B
Hi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||One of the ways:
http://support.microsoft.com/default...b;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>> SQL 2K
>> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
>> NAME,
>> SUPVR, MOD_DATE
>> I want to retain just one row for unique (ID, DPTID) combination, doesn't
>> matter with values of rest of the columns.
>> Thanks In Advance
>> John B
>>
>>
>>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
--
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> > Hi
> >
> > Maybe if mod_date is unique for each id,dptid then
> >
> > DELETE FROM DptAct
> > WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> > DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> >
> > John
> >
> > "MS User" wrote:
> >
> >> SQL 2K
> >>
> >> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
> >> NAME,
> >> SUPVR, MOD_DATE
> >>
> >> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> >> matter with values of rest of the columns.
> >>
> >> Thanks In Advance
> >>
> >> John B
> >>
> >>
> >>
> >>
> >>
> >>
>
>

Delete Column Problems

I created a report with say 5 columns, then decide I only want 4 of them.
My approach was:
1) go to data area and remove from query
2) go to Layout Tab and remove from display
But when I preview it - I get error - it still seems to always be looking
for the column I deleted - what can I do?make sure there are no referneces to that column or field thru any filters
or the where statement in the data area.
"Joe" <hortoristic@.gmail dot com> wrote in message
news:eAINil6yGHA.4648@.TK2MSFTNGP04.phx.gbl...
>I created a report with say 5 columns, then decide I only want 4 of them.
> My approach was:
> 1) go to data area and remove from query
> 2) go to Layout Tab and remove from display
> But when I preview it - I get error - it still seems to always be looking
> for the column I deleted - what can I do?
>|||Go to DataSet,Go to Fields,remove the unwanted column.it should work
fine now
Regards
Raj Deep.A
Ben Watts wrote:
> make sure there are no referneces to that column or field thru any filters
> or the where statement in the data area.
> "Joe" <hortoristic@.gmail dot com> wrote in message
> news:eAINil6yGHA.4648@.TK2MSFTNGP04.phx.gbl...
> >I created a report with say 5 columns, then decide I only want 4 of them.
> >
> > My approach was:
> > 1) go to data area and remove from query
> > 2) go to Layout Tab and remove from display
> >
> > But when I preview it - I get error - it still seems to always be looking
> > for the column I deleted - what can I do?
> >sql

Delete any records in table 1 that are not in table 2 (tables joined on 2 columns)

I have two tables. The primary key for both tables is comprised of 2 columns.

How do I delete any records in table 1 that are not in table 2.

e.g.

if this was the data in the 2 tables

Table 1

col1 col2 col 3

0 0 0

0 1 0

Table 2

col1 col2 col3

0 0 0

I would like to delete the records containg the values 0, 1, 0 from table 1 because the is no record in table 2 with the values 0, 1 as the primary key.

Hi,

You can do this as follow (by suggesting that COL1 and COL2 are the key columns):

DELETE FROM table1
FROM table1
LEFT OUTER JOIN table2
ON table1.COL1 = table2.col1
AND table1.COL2 = table2.col2
WHERE table2.COL1 IS NULL

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Thanks a million. It seems strange to me the way the From caluse is included twice but it works!|||

the following query will do..

Delete from table1 Where Not Exists(Select 1 From table2 Where table1.Col1=Table2.Col1 and Table1.Col2=Table2.Col2)

Defining a concatenated column size

I have a column within a view that is a concatenation of several different columns of another table. It goes something like: tblA.columnA + '-' tblA.columnB + '-' tblA.columnC + '-' tblA.columnD As 'NewColumn'
Is it possible to define the size of the 'NewColumn' within the view? I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error:
Line 1: Incorrect syntax near '*'.
In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar(*), which is causing the error.
Any help would be appreciated.
Thanks.
On Fri, 18 Jun 2004 07:49:01 -0700, Kirk wrote:

>I have a column within a view that is a concatenation of several different columns of another table. It goes something like: tblA.columnA + '-' tblA.columnB + '-' tblA.columnC + '-' tblA.columnD As 'NewColumn'
>Is it possible to define the size of the 'NewColumn' within the view? I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error:
>Line 1: Incorrect syntax near '*'.
>In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar(*), which is causing the error.
>Any help would be appreciated.
>Thanks.
Hi Kirk,
Define the column as nvarchar(4000) (the maximum length). Or, if you want
the max. length to be shorter, use a lower number. The result of the
concatenation will be trimmed to the max. length the column holds (and you
may also choose to trim explicitly, using LEFT or CAST function).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

define unique keys

I have a primary key (column name is emp_id) in employee table. Also,
I would like to make a combination of other two columns is unique.
(combination of officecode field and claimno field must be unique).
how can I implement this uniquess in ms sql 2000? thank you.On 17 Oct 2007 13:41:28 -0700, TGEAR wrote:

Quote:

Originally Posted by

>I have a primary key (column name is emp_id) in employee table. Also,
>I would like to make a combination of other two columns is unique.
>(combination of officecode field and claimno field must be unique).
>how can I implement this uniquess in ms sql 2000? thank you.

Hi TGEAR,

CREATE TABLE Demo
(PrimaryKeyColumn int NOT NULL
,HalfOfOtherKeyColumn int NOT NULL
,OtherHalfOfOtherKey int NOT NULL
,SomeOhterKeyForFun int NOT NULL
,CONSTRAINT pk_Demo PRIMARY KEY (PrimaryKeyColumn)
,CONSTRAINT uq_Demo UNIQUE (HalfOfOtherKeyColumn, OtherHalfOfOtherKey)
);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||TGEAR wrote:

Quote:

Originally Posted by

I have a primary key (column name is emp_id) in employee table. Also,
I would like to make a combination of other two columns is unique.
(combination of officecode field and claimno field must be unique).
how can I implement this uniquess in ms sql 2000? thank you.

alter table employee
add constraint officecode_claimno
unique (officecode, claimno)

define row delimiter to fixed width files

hi all,

is there a way to define a row delimiter to fixed width files? such as this one:

1122333

4455666

7788999

in this file i have 3 columns that are fixed (col1.width = 2, col2.width = 2, col3.width = 3) but have {CR}-{LF} as the row delimiter. when i try to create a flat file connection to these kind of files- he always reads the CR-LF as part of the file text, and there is no place where i can define the row delimiter if i have.

thanks for your help!!!

Create and extra 2 byte field at the end for the import of the CR/LF and just ignore the field in your transforms.

Or use the "Ragged Right" option for the file.|||Have no idea - the work around it to add additional column of size 2 to capture CRLF.|||

What we have here is a terminology issue. The down-side of which is that fixed width files have no row-delimiters, that is not valid fixed width format. The up-side is that ragged right is exactly what you want. It has confused me a couple of times too.

As an aside you could use Fixed Width if you really wanted. You would increase your specified row length by 2, such that it includes the CR_LF if it does not already. This should be easy to see in the Columns UI. Then add an extra two character column such that it just has the CR+LF characters in. You can then ignore this column downstream, just don't use it in any transforms, especially your destination(s).

From Books Online - Format:

Delimited

Columns are separated by delimiters, specified on the Columns page.

Fixed width

Columns have a fixed width.

Ragged right

Ragged right files are files in which every column has a fixed width, except for the last column. It is delimited by the row delimiter.

Default Values in Queries

Hello, I'm trying not to have NULL values in columns.
So I defined a default value ('n.d.') on a column named DDT.
How can i retrieve the rows with the default value in DDT column ?
I mean:
select OrderID, DateOfOrder
from
OrderTable
where
CustomerID=@.CustID
and
DDT is [DDT Column DEFAULT]
instead of :
select OrderID, DateOfOrder
from
OrderTable
where
CustomerID=@.CustID
and
DDT is NULL (or DDT='n.d.')
How does the query change if i define a Default Value DF_NULL char(4) = 'n.d
.'
and assign the defalut value of DDT column to DF_NULL ?
Thank you for help.
MicheleMichele wrote:
> Hello, I'm trying not to have NULL values in columns.
So, have you set the column to "NOT NULL"? That's the best way to prevent
NULLS from being stored in the column ...

> So I defined a default value ('n.d.') on a column named DDT.
> How can i retrieve the rows with the default value in DDT column ?
> I mean:
> select OrderID, DateOfOrder
> from
> OrderTable
> where
> CustomerID=@.CustID
> and
> DDT is [DDT Column DEFAULT]
> instead of :
> select OrderID, DateOfOrder
> from
> OrderTable
> where
> CustomerID=@.CustID
> and
> DDT is NULL (or DDT='n.d.')
> How does the query change if i define a Default Value DF_NULL char(4)
> = 'n.d.' and assign the defalut value of DDT column to DF_NULL ?
> Thank you for help.
> Michele
If you set the DDT column to "NOT NULL", then it will never contain NULL.
Are you saying that you won't know at runtime what the default value for DDT
is? Why wouldn't you just use:
WHERE DDT='n.d.'
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> So, have you set the column to "NOT NULL"? That's the best way to prevent
> NULLS from being stored in the column ...
Yes the column DDT is set to NOT NULL.

> Are you saying that you won't know at runtime what the default value for D
DT
> is?
Yes.

>Why wouldn't you just use:
> WHERE DDT='n.d.'
Because for some NOT NULL columns the default is 'n.d.' for others is
'<unknown>' for others '000000', so I'd like to treat the default value of
the column as a parameter for the query (if possible), like a ... where DDT
is NULL (in case of DDT column NULL, but this is not the case)
Thank's.|||Michele wrote:
> Yes the column DDT is set to NOT NULL.
>
> Yes.
>
> Because for some NOT NULL columns the default is 'n.d.' for others is
> '<unknown>' for others '000000', so I'd like to treat the default
> value of the column as a parameter for the query (if possible), like
> a ... where DDT is NULL (in case of DDT column NULL, but this is not
> the case)
>
I've never attempted to do this (I always know what the default values are
in my columns ... <g,d&r> )
I suppose you could create a scaler udf that uses the sp_columns procedure,
or queries the INFORMATION_SCHEMA.Columns table, to retrieve the column's
default value.
WHERE DDT=fColDefault(DDT)
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Default value in SP

Hello group,
I use SP to update value in myTable,
MyTable have a few columns (about 15) and I want update not all field.
Sometime I need update column 2,3,5 and next time 4,5,11.
If parametr is not defined leave current value
My question is how set as default value of parameter equal current value in
table?
My template of procedure (SP)
alter procedure MySP
(
@.idRow int //it is row id and ever is define
@.par1 int,
@.par2 int,
....
@.par15 int,
)
AS
Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
@.idRow
I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
MySP 1,,,,,...,45
Thx PawelRUPDATE MyTable SET col1 = COALESCE(@.par1, col1), ...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:%23Q95ibzRFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value
> in table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>|||One option can be to use NULL as the default value and use functions ISNULL
or COALESCE in the UPDATE statement.
alter procedure MySP
(
@.idRow int //it is row id and ever is define
@.par1 int,
@.par2 int = null,
@.par3 int = null,
@.par4 intl,
@.par5 int = null,
....
@.par15 int,
)
AS
set nocount on
Update MyTable
set col1=@.par1, col2=coalesce(@.par2, col2), col3=coalesce(@.par3, col3),
col5=coalesce(@.par5, col5), ... col15=@.par15 where idRow = @.idRow
return @.@.error
go
"PawelR" wrote:

> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value i
n
> table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>
>|||let the default value be null, or some other non-applicable value. something
like this:
alter procedure MySP
(
@.idRow int
@.par1 int=null,
@.par2 int=null,
....
@.par15 int,
)
AS
Update MyTable set col1=coalesce(@.par1,col1),
col2=coalesce(@.par2,col2),
..
where idRow = @.idRow
dean
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:%23Q95ibzRFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value
> in table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>

Default value for Datetime column

Hi
I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
My tables have several columns that are defined as datetime fields which
need to default to the empty date that I have chosen.
I type in 01/01/1753 as the default value of the table designer, however
when I insert a new line into any of the tables, the value that gets
inserted is 01/01/1900.
Does anyone know why this is happening? I am assuming that I have some
configuration value set incorrectly.
I realise that I could simply use 1900 as the empty date. I could also
include the field and a value in the insert statement, but that isn't the
point. I'd rather know why the system behaves like this.
Hope someone can help
Thanks in anticipationDATETIME is just that. It is not date or time, it is both. If you set the
default value of 1753-01-01 then it is 1753-01-01 12:00:00.000 AM if no
value is inserted. If you enter a date or a time, *that* value determines
the other part. If you enter a time only in Query Analyzer, you get
1900-01-01 hh:mm:ss, if you do it in Enterprise Manager, you get 1899-12-31
hh:mm:ss. A value you enter overrides the entire value, even if you only
specify a portion. You can't say the date is x if I enter a time of y. If
you want a specific date to go along with your time, enter that date as
well. You should handle this in a stored procedure so that it is
consistent.
http://www.aspfaq.com/2206
http://www.aspfaq.com/2455
"Mark_S" <Mark_S@.nospam.nospam> wrote in message
news:%23$GRC7$1FHA.2056@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
>
> My tables have several columns that are defined as datetime fields which
> need to default to the empty date that I have chosen.
>
> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
>
> Does anyone know why this is happening? I am assuming that I have some
> configuration value set incorrectly.
>
> I realise that I could simply use 1900 as the empty date. I could also
> include the field and a value in the insert statement, but that isn't the
> point. I'd rather know why the system behaves like this.
>
> Hope someone can help
>
> Thanks in anticipation
>|||>> I am using 01/01/1753 to define the "Empty Date" for my datetime fields [sic].
<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. One of the MANY differences in
fields and columns is that a field can be missing or empty while a
column cannot.
Next, the proper format for dates in SQL is '1753-01-01' as per the
ISO-11179 Standards.
That has to do with the internal representation of DATETIME data types
in SQL Server. And Great Britain and its colonies went to the
Gregorian (Common Era) calendar in 1752, nobody started on
'1753-01-01' as far as I can tell.
No, you cannot. That is an integer and not a temporal data element at
all. Temporal data types, like all other SQL data types, can have a
NULL value. You can also add a DEFAULT CURRENT_TIMESTAMP or
DEFAULT '1753-01-01' clause to the DDL.
Exactly what are you trying to do?|||> Exactly what are you trying to do?
Sounds like when he enters a "datetime" value, like '4:30 PM', he wants the
date that goes with that to be 1753-01-01.
A|||Mark_S (Mark_S@.nospam.nospam) writes:
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
Why not use NULL?
There are those who think NULL are evil, but just face it. Any in-band
value that is used to mean "no value" is going to cause problem. If
nothing else, there can be confusion about which that value is.
(Although I will have to admit having used 17530101, in one place in our
system. I had a need to distinguish between "it has always been this
way" (which is 17530101) and "it has never been this way" (which is NULL).)

> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
When you specify a string for a datetime value in SQL Server, all parts
have a default value which is applied. So an empty string, results in
1900-01-01 00.00.00:000.
Since you get this value, you are apparently explicitly specifying a value.
Had you specified a NULL value, you would have gotten a NULL (or a NOT
NULL violation). The default value is only used when nothing at all
is specified.
This script illustrates:
CREATE TABLE datedemo (a int NOT NULL,
d datetime NULL DEFAULT '17530101')
go
INSERT datedemo (a) VALUES (1)
INSERT datedemo (a, d) VALUES (2, NULL)
INSERT datedemo (a, d) VALUES (3, '')
INSERT datedemo (a, d) VALUES (4, '12:24:34')
go
SELECT * FROM datedemo ORDER BY a
go
DROP TABLE datedemo
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I type in 01/01/1753 as the default value of the table designer
Without quotes, this is likely being interpreted as 1 divided by 1 divided
by 1753, which is going to round (dur to integer math) to 0, which is the
base date, 1900-01-01.
If you use a proper and unambiguous format for your date, you can avoid this
kind of thing. (And don't use the table designer.)
CREATE TABLE dbo.MyTable
(
i INT IDENTITY(1,1),
dt DATETIME NOT NULL DEFAULT '17530101'
);
GO
SET NOCOUNT ON;
INSERT dbo.MyTable DEFAULT VALUES;
INSERT dbo.MyTable(dt) SELECT '19000101';
INSERT dbo.MyTable(dt) SELECT '20050101';
INSERT dbo.MyTable(dt)
SELECT CONVERT(DATETIME, 01/01/1753);
GO
SELECT i,dt FROM dbo.MyTable ORDER BY i,dt;
GO
DROP TABLE dbo.MyTable;
GO|||The group is on a rampage for some odd reason.
Make sure you didn't accidentally define the data type as SMALLDATETIME,
which has a lower bound of '1900-01-01 00:00:00.000'.
Sincerely,
Anthony Thomas
"Mark_S" <Mark_S@.nospam.nospam> wrote in message
news:%23$GRC7$1FHA.2056@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
>
> My tables have several columns that are defined as datetime fields which
> need to default to the empty date that I have chosen.
>
> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
>
> Does anyone know why this is happening? I am assuming that I have some
> configuration value set incorrectly.
>
> I realise that I could simply use 1900 as the empty date. I could also
> include the field and a value in the insert statement, but that isn't the
> point. I'd rather know why the system behaves like this.
>
> Hope someone can help
>
> Thanks in anticipation
>

Default Value Calculation

Hi,
Is there a way to calculate the Default Value of a column with other data
columns? Using "date_of_birth" column and "screen_date" columns, I would
like to calculate the "age" automatically with the Default Value definition.
Please let me know if it's possible.
YC> Is there a way to calculate the Default Value of a column with other data
> columns? Using "date_of_birth" column and "screen_date" columns, I would
> like to calculate the "age" automatically with the Default Value
> definition.
Why would you need to store this data? If you have 2 in one column and 2 in
another column, do you really need to store 4 in a "sum" column, when you
can do this in a view or procedure?
Anyway, I think you want to use a computed column, not a default value.
E.g.:
CREATE TABLE dbo.MyTable
(
db SMALLDATETIME,
sd SMALLDATETIME,
age_in_days AS CONVERT(INT, DATEDIFF(DAY, db, sd))
)
GO
SET NOCOUNT ON
INSERT dbo.MyTable(db, sd) SELECT '20050101', GETDATE())
INSERT dbo.MyTable(db, sd) SELECT '20050501', '20050505')
SELECT * FROM dbo.MyTable
GO
DROP TABLE dbo.MyTable
GO|||Hi
It is not clear why you would want to store the age in this way. Current Age
would be SELECT DATEDIFF(yy,dob, getdate()) which could be added to an
INSTEAD OF trigger. If you want to have a more up-to-date age you can use
the function in a view e.g.
CREATE VIEW vw_Users AS
SELECT Name, dob, DATEDIFF(yy,dob, getdate()) AS Age
FROM tbl_users
SELECT Name, dob, age
FROM vw_Users
John
"Asp Psa" wrote:

> Hi,
> Is there a way to calculate the Default Value of a column with other data
> columns? Using "date_of_birth" column and "screen_date" columns, I would
> like to calculate the "age" automatically with the Default Value definitio
n.
> Please let me know if it's possible.
> YC
>
>|||John's formula does not break on the birthday. It will tell you how old
someone will be at the end of a year.
Aaron's formula can be tweaked to provide a person's age as of today. It
can be put into a computed column as Aaron has suggested or in a user-define
d
function or stored procedure.
CONVERT(INT, DATEDIFF(DAY, dob, getdate()) / 365.25) as age
Personlly, I would not store the age in a table because it's subject to
change each day. Or, if you need what year it is for the person (John's
formula) then you have to refresh the table data once per year.
Hope that helps,
Joe
"John Bell" wrote:
> Hi
> It is not clear why you would want to store the age in this way. Current A
ge
> would be SELECT DATEDIFF(yy,dob, getdate()) which could be added to an
> INSTEAD OF trigger. If you want to have a more up-to-date age you can use
> the function in a view e.g.
> CREATE VIEW vw_Users AS
> SELECT Name, dob, DATEDIFF(yy,dob, getdate()) AS Age
> FROM tbl_users
> SELECT Name, dob, age
> FROM vw_Users
> John
> "Asp Psa" wrote:
>|||> Why would you need to store this data?
Aaron,
sometimes we do that to speed queries up.
For instance, if there are columns order_date and shipment_date,
turnaround (days between ordering and shipment) and shipment_month can
be calculated no problem. But if there are many frequently running
queries involving these expressions, such as
select ...
where turnaround>10
select sum(amount), shipment_month, turnaround
from orders
group by shipment_month, turnaround
it really helps to create an index on (turnaround, shipment_month,
amount)
Makes sense?|||> Makes sense?
Yes, of course. I was asking if the OP really needed to do it, I don't
recall saying, "don't do that, it's stupid and doesn't make sense."|||I agree that this is not the way to go. It would be best to calculate
it in a view. BTW, sometimes age is static. For example a patient's
age is that at the time of admission. If you really need a separate
field for this, you could create a simple trigger.|||Joe
As people are born on different days, you will need to refresh it every day
(just in case!)!
John
"Joe from WI" wrote:
> John's formula does not break on the birthday. It will tell you how old
> someone will be at the end of a year.
> Aaron's formula can be tweaked to provide a person's age as of today. It
> can be put into a computed column as Aaron has suggested or in a user-defi
ned
> function or stored procedure.
> CONVERT(INT, DATEDIFF(DAY, dob, getdate()) / 365.25) as age
> Personlly, I would not store the age in a table because it's subject to
> change each day. Or, if you need what year it is for the person (John's
> formula) then you have to refresh the table data once per year.
> Hope that helps,
> Joe
> "John Bell" wrote:
>|||Age changes from day to day, so put the calculation in a VIEW. Do not
store computed data.|||I might have misread these posts.
A computed column is persisted only when required, for instance at the time
of use on the select clause.
Its not calculated and stored, unless its value is deterministic in which
case you would index and only then is the value persisted.
Its a lot more efficient and easier to do these age things based on timenow
using a computed column rather than a view.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C1B2D6CE-5BE3-46A3-AE8A-72E95161011D@.microsoft.com...
> Joe
> As people are born on different days, you will need to refresh it every
> day
> (just in case!)!
> John
> "Joe from WI" wrote:
>

Default value being a concatenation of other columns

Hello all,

Does anybody know if it is possible to set the default value of a
field to equal the concatenation of 2 other fields?
I.E.
Field 1 = 123
Field 2 = 345
Field 3 = (Field1 + Field2) = 123345

This is easy enough to do using a stored procedure, but I'm trying to
use the import tool and so need to do the concatenation by default.

Many thanks,
Drew.Drew (andylole@.gmail.com) writes:

Quote:

Originally Posted by

Hello all,
>
Does anybody know if it is possible to set the default value of a
field to equal the concatenation of 2 other fields?
I.E.
Field 1 = 123
Field 2 = 345
Field 3 = (Field1 + Field2) = 123345
>
This is easy enough to do using a stored procedure, but I'm trying to
use the import tool and so need to do the concatenation by default.

No, but you can define a computed column:

CREATE TABLE nisse (kalle int NOT NULL,
lisa int NOT NULL,
putte AS kalle + lisa)

Of course, if you need to update the columns independently of each
other, this is not any feasible solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

default vales

Hi,
How can I find table name, column name of all columns having default value
''
Is there any system table store the defalt values.
ThanksTry,
select table_name, column_default
from information_schema.columns
where column_default = '('''')'
HTH
Prasad
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:en4e1T2WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I find table name, column name of all columns having default
value
> ''
> Is there any system table store the defalt values.
> Thanks
>|||Include the column name as below,
Try,
select table_name, column_name, column_default
from information_schema.columns
where column_default = '('''')'
HTH
Prasad
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:en4e1T2WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I find table name, column name of all columns having default
value
> ''
> Is there any system table store the defalt values.
> Thanks
>|||Binu,
SELECT column_name,table_name,column_default
FROM information_schema.columns
WHERE column_default = '('''')'
OR column_default = '(space(0))'
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:en4e1T2WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I find table name, column name of all columns having default
value
> ''
> Is there any system table store the defalt values.
> Thanks
>