check previous row in the table

Hi...

Is there any way to check previous row in SQL Query?

I have a table with these column :
Name1
Name2
Audit_Time (datetime)
Changes

I want to delete record from database in which the Audit_time is <'01/05/2004'.
However before deletion, I want to check, if the Changes value is 'OLD' And the previous value is 'NEW', I will check the Audit_time of the NEW instead of OLD.

Table :
Row Name1 Name2 Audit_Time(mm/dd/yyyy) Changes
1 ABCD EFGH '01/01/2004' ADD
2 ABCD EFGHIJ '01/04/2004' NEW
3 ABCD EFGH '01/04/2004' OLD
4 Klarinda Rahmat '02/08/2004' NEW
5 Klarinda Rahmat '01/04/2004' OLD

In this case, I want to delete row 1,2,3 Where the audit_time are < '01/05/2004'.
Row 5 the audit_time also < '01/05/2004', however the changes='OLD' and the previous value changes='NEW', so I will check the Audit_Time of row 4 which is not < '01/05/2004'.
So I can't delete row5.

Is there any way to check previous row or the row before a specific row in SQL.
Any suggestion is welcomed.
Thank you in advanced.You can use a Cursor in a Stored Procedure. It would be very complex though...|||Check out the EXISTS keyword (assuming SQL server)...sql

check numeric data type in SSIS

Dear All,

the situation is that i have a column data comes from flat file and all i want to do is to check that the incoming column is numeric(12,3) and if the incoming data exceed that size "12,3" exception or redirect the row is happened.

the problem that i try to apply that with the data conversion or Derived column component but it in case of the scale of the incoming data exceed 3 the component trim until 3 scale.

i also try to perform it with the flat file data source component but i face a problem that if the data in the column is empty then flat file data source component read the numeric column as Zero

i hope someone help me coz i need to handle it soon.

best wishes

Maylo

Here is a thought for your Scale 3 situation.

Could you try importing the data into column X as a larger datatype, say (20,5).

Then use two derived value steps to create a new column Y that is the result of conversion from (20,5) to (12,3) and then back to (20,5).

Now compare the value in column X with the value in column Y.

If X is a valid (12,3) value, then it must now have the same value as Y. Otherwise it will be different.

Simulation:

Flat file value: 123456.789

imported to X (20,5): 123456.789

Converted to Y (12,3): 123456.789

Converted back to Y (20,5): 123456.789

(X == Y) = true

Flat file value: 1234.56789

imported to X (20,5): 1234.56789

Converted to Y (12,3): 1234.567

Converted back to Y (20,5): 1234.567

(X == Y) = false

(in my VB days, we would have achieved something like this by going:

y = int(x*1000) / 1000

if x=y then msgbox "All is sweet." else msgbox "Your value has too many decimal places."

|||

thanx SOoooooo much it helps me alot

best wishes

Maylo

|||

The way I normally get round this is to use a script component.

Feed all available output columns from your flat file into the script component.

In the script component add an extra outpt column as a boolean called, for example, blnOK

In the script component's ProcessInputRow Sub add code similar to the following

If IsNumeric(Row.RowToCheck) Then

Row.blnOK = True

Else

Row.blnOK = False

End If

where RowToCheck is the particular row from the flat file you wish to check.

Then use a conditional split transformation to check the value of your new column blnOK. You can then direct your rows accordingly, ie, where blnOK is TRUE rows would go to your default table and where blnOK is FALSE rows could go to and error table.

This is a simple example but you could extend the code by creating a function to check for any data type, string format etc or even create a DLL, which you can re-use for similar situations though this may be a bit over the top.

Hope this helps

|||That RowToCheck in above should refer to the column/field to check NOT a row. Sorrysql

check numeric data type in SSIS

Dear All,

the situation is that i have a column data comes from flat file and all i want to do is to check that the incoming column is numeric(12,3) and if the incoming data exceed that size "12,3" exception or redirect the row is happened.

the problem that i try to apply that with the data conversion or Derived column component but it in case of the scale of the incoming data exceed 3 the component trim until 3 scale.

i also try to perform it with the flat file data source component but i face a problem that if the data in the column is empty then flat file data source component read the numeric column as Zero

i hope someone help me coz i need to handle it soon.

best wishes

Maylo

Here is a thought for your Scale 3 situation.

Could you try importing the data into column X as a larger datatype, say (20,5).

Then use two derived value steps to create a new column Y that is the result of conversion from (20,5) to (12,3) and then back to (20,5).

Now compare the value in column X with the value in column Y.

If X is a valid (12,3) value, then it must now have the same value as Y. Otherwise it will be different.

Simulation:

Flat file value: 123456.789

imported to X (20,5): 123456.789

Converted to Y (12,3): 123456.789

Converted back to Y (20,5): 123456.789

(X == Y) = true

Flat file value: 1234.56789

imported to X (20,5): 1234.56789

Converted to Y (12,3): 1234.567

Converted back to Y (20,5): 1234.567

(X == Y) = false

(in my VB days, we would have achieved something like this by going:

y = int(x*1000) / 1000

if x=y then msgbox "All is sweet." else msgbox "Your value has too many decimal places."

|||

thanx SOoooooo much it helps me alot

best wishes

Maylo

|||

The way I normally get round this is to use a script component.

Feed all available output columns from your flat file into the script component.

In the script component add an extra outpt column as a boolean called, for example, blnOK

In the script component's ProcessInputRow Sub add code similar to the following

If IsNumeric(Row.RowToCheck) Then

Row.blnOK = True

Else

Row.blnOK = False

End If

where RowToCheck is the particular row from the flat file you wish to check.

Then use a conditional split transformation to check the value of your new column blnOK. You can then direct your rows accordingly, ie, where blnOK is TRUE rows would go to your default table and where blnOK is FALSE rows could go to and error table.

This is a simple example but you could extend the code by creating a function to check for any data type, string format etc or even create a DLL, which you can re-use for similar situations though this may be a bit over the top.

Hope this helps

|||That RowToCheck in above should refer to the column/field to check NOT a row. Sorry

Check if image column is empty

Currently I use the follow Select statement to test if a non-nullable image
column is empty and not null:
Select Case When Substring(ImageColumn,1,1)='' Then 1 Else 0 End As IsEmpty
Can any body show me a better way to check if a column (non-nullable column)
of image data type is empty.DataLength(ColName) will be zero (0) if it's empty...
"krygim" wrote:

> Currently I use the follow Select statement to test if a non-nullable imag
e
> column is empty and not null:
> Select Case When Substring(ImageColumn,1,1)='' Then 1 Else 0 End As IsEmpt
y
> Can any body show me a better way to check if a column (non-nullable colum
n)
> of image data type is empty.
>
>|||Or, actually, just check if the column's value itself = '' (empty string)...
That should work. you don't need to attempt to extract the first character
and test that...
"krygim" wrote:

> Currently I use the follow Select statement to test if a non-nullable imag
e
> column is empty and not null:
> Select Case When Substring(ImageColumn,1,1)='' Then 1 Else 0 End As IsEmpt
y
> Can any body show me a better way to check if a column (non-nullable colum
n)
> of image data type is empty.
>
>|||Thanks
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:380D392D-B82A-493E-AA22-C137F4599577@.microsoft.com...
> DataLength(ColName) will be zero (0) if it's empty...
> "krygim" wrote:
>
image
IsEmpty
column)|||I got the error message: "The text, ntext, and image data types cannot be
compared or sorted."
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:3ADDA38D-0AC0-4377-9933-182222F35875@.microsoft.com...
> Or, actually, just check if the column's value itself = '' (empty
string)...
> That should work. you don't need to attempt to extract the first
character
> and test that...
>
> "krygim" wrote:
>
image
IsEmpty
column)|||Yes, you're right, DataLength() is the only other way...
"krygim" wrote:

> I got the error message: "The text, ntext, and image data types cannot be
> compared or sorted."
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:3ADDA38D-0AC0-4377-9933-182222F35875@.microsoft.com...
> string)...
> character
> image
> IsEmpty
> column)
>
>

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004
You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
...
David Portas
SQL Server MVP

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
...
--
David Portas
SQL Server MVP
--

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
..
David Portas
SQL Server MVP
--sql

Check if Column has an index

Hi All
Ho do I go about determining a list of all columns in a table that have an
index on them?
Thanks
Hi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks
|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:

> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>
>
|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...[vbcol=seagreen]
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol1,IndCol2, IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,In dCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16,SegNa me,FullTextKey,Descending,Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,I ndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:

> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
>
>

Check if Column has an index

Hi All
Ho do I go about determining a list of all columns in a table that have an
index on them?
ThanksHi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:

> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>
>|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...[vbcol=seagreen]
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
>|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol
1,IndCol2,IndCol3,IndCol4,IndCol5,In
dCol6,IndCol7,IndCol8,IndCol9,IndCol10,I
ndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndC
ol16,SegName,FullTextKey,Descending,
Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5
,IndCol6,IndCol7,IndCol8,IndCol9,Ind
Col10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndC
ol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:

> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
>
>

Check if Column has an index

Hi All
Ho do I go about determining a list of all columns in a table that have an
index on them?
ThanksHi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:
> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> > Hi All
> >
> > Ho do I go about determining a list of all columns in a table that have an
> > index on them?
> >
> > Thanks
>
>|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
>> Hi David
>> For starters,
>> EXEC sp_helpindex <tablename>
>> will tell you all the indexes and what their key columns are.
>> If you need an actual list of columns, please specify what version this
>> is
>> for.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>> > Hi All
>> >
>> > Ho do I go about determining a list of all columns in a table that have
>> > an
>> > index on them?
>> >
>> > Thanks
>>|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16,SegName,FullTextKey,Descending,Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:
> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
> > Hi Kalen
> >
> > Thanks for the promt responce.
> >
> > I am running SQL Server 2000 and I need an actual list of columns.
> >
> > Thanks
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi David
> >>
> >> For starters,
> >> EXEC sp_helpindex <tablename>
> >>
> >> will tell you all the indexes and what their key columns are.
> >>
> >> If you need an actual list of columns, please specify what version this
> >> is
> >> for.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> >> > Hi All
> >> >
> >> > Ho do I go about determining a list of all columns in a table that have
> >> > an
> >> > index on them?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>

Check if column (int) is true or false (bool)

Is there some way I may "convert" an int column to boolean with the
SQL query? I have a database with an int column containing only 0s and
1s - using it as a Boolean. Now, I need the Boolean values false and
true in order to use the .net checkbox.
I have tried the following:
SELECT userID, (chkName = 1) AS bChkName, name
FROM User
When chkName = 1 it will return true, and vice versa when it equals 0.
This should produce something like this:
userID bChkName name
2 true Peter
3 true Linda
4 false John Doe
according to the int in chkName.
This of course didn't work and now I am hoping for your help... do you
know of any solution?
royend.
This really does not solve my problem, as it returns a string and not
a Boolean value.
The asp:checkbox requires a boolean in order to become checked or
unchecked.
In worst case scenario I'll have to code inside some of Visual's
predefined and automatically added code...
Still, I am a bit surprised that SQL cannot return a bool value for
one of its column based on a simple test.
Thanks for your answer though.
royend
On 25 Jun, 13:02, "vt" <vinu.t.1...@.gmail.com> wrote:
> royend
> there is no direct way to do this
> select userID, case when chkName =1 then 'true' else 'false',name from user
> Regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"royend" <roy...@.gmail.com> wrote in message
> news:1182768635.843541.88960@.c77g2000hse.googlegro ups.com...
>
>
>
>
> - Vis sitert tekst -
|||> This really does not solve my problem, as it returns a string and not
> a Boolean value.
And ASP.Net can't turn 'true' or 'false' into Boolean values? Isn't that an
ASP.Net problem, not a SQL Server problem?
|||On Mon, 25 Jun 2007 04:44:18 -0700, royend wrote:

>This really does not solve my problem, as it returns a string and not
>a Boolean value.
Hi royend,
Unfortunately, SQL Server doesn't support boolean values. Quite logical,
if you consider that all predicate logic in an RDBMS is three-valued,
whereas boolean algebra is two-valued. Put a PITA in some situations
nonetheless.
The closest SQL Server has to offer would be bit. Something like
CASE WHEN chkName = 1 THEN CAST(1 as bit) ELSE CAST(0 AS bit) END
But then you'd have to rely on the conversion from bit to boolean by ASP
treating 1 as true and 0 as false - frankly, I'd rather use code that
returns a string 'True' or 'False' (maybe shortened to 'T' and 'F') and
convert it with explicit ASP code then to rely on the implicit
conversion from SQL bit to ASP bool (that is, as far as I know,
undocumented) doing what I hope it does, and still doing it next year.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Check if column (int) is true or false (bool)

Is there some way I may "convert" an int column to boolean with the
SQL query? I have a database with an int column containing only 0s and
1s - using it as a Boolean. Now, I need the Boolean values false and
true in order to use the .net checkbox.
I have tried the following:
SELECT userID, (chkName = 1) AS bChkName, name
FROM User
When chkName = 1 it will return true, and vice versa when it equals 0.
This should produce something like this:
userID bChkName name
2 true Peter
3 true Linda
4 false John Doe
according to the int in chkName.
This of course didn't work and now I am hoping for your help... do you
know of any solution?
royend.royend
there is no direct way to do this
select userID, case when chkName =1 then 'true' else 'false',name from user
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"royend" <royend@.gmail.com> wrote in message
news:1182768635.843541.88960@.c77g2000hse.googlegroups.com...
> Is there some way I may "convert" an int column to boolean with the
> SQL query? I have a database with an int column containing only 0s and
> 1s - using it as a Boolean. Now, I need the Boolean values false and
> true in order to use the .net checkbox.
> I have tried the following:
> SELECT userID, (chkName = 1) AS bChkName, name
> FROM User
> When chkName = 1 it will return true, and vice versa when it equals 0.
> This should produce something like this:
> userID bChkName name
> 2 true Peter
> 3 true Linda
> 4 false John Doe
> according to the int in chkName.
> This of course didn't work and now I am hoping for your help... do you
> know of any solution?
> royend.
>|||This really does not solve my problem, as it returns a string and not
a Boolean value.
The asp:checkbox requires a boolean in order to become checked or
unchecked.
In worst case scenario I'll have to code inside some of Visual's
predefined and automatically added code...
Still, I am a bit surprised that SQL cannot return a bool value for
one of its column based on a simple test.
Thanks for your answer though.
royend
On 25 Jun, 13:02, "vt" <vinu.t.1...@.gmail.com> wrote:
> royend
> there is no direct way to do this
> select userID, case when chkName =1 then 'true' else 'false',name from user
> Regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"royend" <roy...@.gmail.com> wrote in message
> news:1182768635.843541.88960@.c77g2000hse.googlegroups.com...
>
> > Is there some way I may "convert" an int column to boolean with the
> > SQL query? I have a database with an int column containing only 0s and
> > 1s - using it as a Boolean. Now, I need the Boolean values false and
> > true in order to use the .net checkbox.
> > I have tried the following:
> > SELECT userID, (chkName = 1) AS bChkName, name
> > FROM User
> > When chkName = 1 it will return true, and vice versa when it equals 0.
> > This should produce something like this:
> > userID bChkName name
> > 2 true Peter
> > 3 true Linda
> > 4 false John Doe
> > according to the int in chkName.
> > This of course didn't work and now I am hoping for your help... do you
> > know of any solution?
> > royend.- Skjul sitert tekst -
> - Vis sitert tekst -|||Try this...
select userID, convert(bit,chkName) as BoolName,name from user|||> This really does not solve my problem, as it returns a string and not
> a Boolean value.
And ASP.Net can't turn 'true' or 'false' into Boolean values? Isn't that an
ASP.Net problem, not a SQL Server problem?|||On Mon, 25 Jun 2007 04:44:18 -0700, royend wrote:
>This really does not solve my problem, as it returns a string and not
>a Boolean value.
Hi royend,
Unfortunately, SQL Server doesn't support boolean values. Quite logical,
if you consider that all predicate logic in an RDBMS is three-valued,
whereas boolean algebra is two-valued. Put a PITA in some situations
nonetheless.
The closest SQL Server has to offer would be bit. Something like
CASE WHEN chkName = 1 THEN CAST(1 as bit) ELSE CAST(0 AS bit) END
But then you'd have to rely on the conversion from bit to boolean by ASP
treating 1 as true and 0 as false - frankly, I'd rather use code that
returns a string 'True' or 'False' (maybe shortened to 'T' and 'F') and
convert it with explicit ASP code then to rely on the implicit
conversion from SQL bit to ASP bool (that is, as far as I know,
undocumented) doing what I hope it does, and still doing it next year.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelissql

Check if column (int) is true or false (bool)

Is there some way I may "convert" an int column to boolean with the
SQL query? I have a database with an int column containing only 0s and
1s - using it as a Boolean. Now, I need the Boolean values false and
true in order to use the .net checkbox.
I have tried the following:
SELECT userID, (chkName = 1) AS bChkName, name
FROM User
When chkName = 1 it will return true, and vice versa when it equals 0.
This should produce something like this:
userID bChkName name
2 true Peter
3 true Linda
4 false John Doe
according to the int in chkName.
This of course didn't work and now I am hoping for your help... do you
know of any solution?
royend.royend
there is no direct way to do this
select userID, case when chkName =1 then 'true' else 'false',name from user
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"royend" <royend@.gmail.com> wrote in message
news:1182768635.843541.88960@.c77g2000hse.googlegroups.com...
> Is there some way I may "convert" an int column to boolean with the
> SQL query? I have a database with an int column containing only 0s and
> 1s - using it as a Boolean. Now, I need the Boolean values false and
> true in order to use the .net checkbox.
> I have tried the following:
> SELECT userID, (chkName = 1) AS bChkName, name
> FROM User
> When chkName = 1 it will return true, and vice versa when it equals 0.
> This should produce something like this:
> userID bChkName name
> 2 true Peter
> 3 true Linda
> 4 false John Doe
> according to the int in chkName.
> This of course didn't work and now I am hoping for your help... do you
> know of any solution?
> royend.
>|||This really does not solve my problem, as it returns a string and not
a Boolean value.
The asp:checkbox requires a boolean in order to become checked or
unchecked.
In worst case scenario I'll have to code inside some of Visual's
predefined and automatically added code...
Still, I am a bit surprised that SQL cannot return a bool value for
one of its column based on a simple test.
Thanks for your answer though.
royend
On 25 Jun, 13:02, "vt" <vinu.t.1...@.gmail.com> wrote:
> royend
> there is no direct way to do this
> select userID, case when chkName =1 then 'true' else 'false',name from us
er
> Regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"royend"
<roy...@.gmail.com> wrote in message
> news:1182768635.843541.88960@.c77g2000hse.googlegroups.com...
>
>
>
>
>
>
> - Vis sitert tekst -|||Try this...
select userID, convert(bit,chkName) as BoolName,name from user|||> This really does not solve my problem, as it returns a string and not
> a Boolean value.
And ASP.Net can't turn 'true' or 'false' into Boolean values? Isn't that an
ASP.Net problem, not a SQL Server problem?

Check if a field exists before ADD a new column.

Hi All.
We have some scripts adding columns to tables. Is there a simple way to
check if the column are there already in the table before running the ADD
command. Just like you get on the table when scripting it?
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CalDates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Thanx all
ghHello,
You can use something like this:
IF NOT EXISTS (SELECT * FROM syscolumns
WHERE id=OBJECT_ID('TableName') AND name='ColumnName')
[...]
Razvan|||if col_length('tb','col') is null
print('col does not exist in tb')
-oj
"Geir Holme" <geir@.multicase.no> wrote in message
news:uA2GTzcPGHA.1532@.TK2MSFTNGP12.phx.gbl...
> Hi All.
> We have some scripts adding columns to tables. Is there a simple way to
> check if the column are there already in the table before running the ADD
> command. Just like you get on the table when scripting it?
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[CalDates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>
> Thanx all
> gh
>

Check for unicode within an ntext

Hey all,
I've got to check an ntext column if it actually does contain unicode characters or not. I would prefer to not have to iterate through every character in each column, is there a simple way to do this in sql server 2000?
Cheers,
-KilkaCAST or CONVERT it to text, and monitor for errors.|||That's a great idea, thanks Blindman.

Cheers,
-Kilka

Check for numeric value

Is it possible to check whether a column (define as char
type) value is numeric using something like
Select * From table_name Where Field1 is numeric ?
Thanks,
BenYou can use the ISNUMERIC function for this:
WHERE ISNUMERIC(colname) = 1
Note, however that this returns 1 if the data can be converted to int,
float, money etc. So things like "E" and "," in the string will pass the
test. If you post what you mean precisely by "numeric" we can possibly give
a better suggestion.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ben" <bluebells88@.yahoo.com> wrote in message
news:2eb201c3a9c4$176cb430$a601280a@.phx.gbl...
> Is it possible to check whether a column (define as char
> type) value is numeric using something like
> Select * From table_name Where Field1 is numeric ?
> Thanks,
> Ben|||Thank your very much for your answer. This one works fine
for my case :)
I've another question: Is it possible to determine if the
column value is NOT alphabet. (using ASCII ? It seems
impossible to me.)
Thanks,
Ben
>--Original Message--
>You can use the ISNUMERIC function for this:
>WHERE ISNUMERIC(colname) = 1
>Note, however that this returns 1 if the data can be
converted to int,
>float, money etc. So things like "E" and "," in the
string will pass the
>test. If you post what you mean precisely by "numeric"
we can possibly give
>a better suggestion.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Ben" <bluebells88@.yahoo.com> wrote in message
>news:2eb201c3a9c4$176cb430$a601280a@.phx.gbl...
>> Is it possible to check whether a column (define as
char
>> type) value is numeric using something like
>> Select * From table_name Where Field1 is numeric ?
>> Thanks,
>> Ben
>
>.
>

Check for missing identity numbers

Is there a way to check for missing identity numbers in a Primary Key column? I have some databases that are not fully normalized and want to check on tables that might have had some records deleted. Thank you.

Do you really want to do this in SSIS? Whilst possible I think a SQL based solution would be much faster, and probably makes more sense. A quick Google will come up with plenty of script samples e.g. http://www.nigelrivett.net/FindGapsInSequence.html|||

If you just need to know the number of 'deleted' rows; you can do a simple substraction of the max identitity number minus the number of rows in the table. If you need a list of the ID's that not exists in the table; then you could use a cursor that from 1 to max identity and checks if the row exists in the table or not....anyway you may find more help in the t-sql forum that is next door

Check for existence of specific value in a dataset

How do I check for the existence of a specific value in a column in a
dataset? For example, I have 'dataset' with 'columnA' and I want to find out
if ANY row in that dataset has a 'columnA' of value 'valueA'. Seems simple
but I'm having trouble. Thanks.
StephanieOn May 22, 12:42 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> How do I check for the existence of a specific value in a column in a
> dataset? For example, I have 'dataset' with 'columnA' and I want to find out
> if ANY row in that dataset has a 'columnA' of value 'valueA'. Seems simple
> but I'm having trouble. Thanks.
> Stephanie
The closest thing to the functionality you want is with and expression
similar to this:
=Max(iif(Fields!columnA.Value = 'valueA', 1, 0))
So if the value exists in the column values, the expression will
return a 1. Otherwise, it will return a 0.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Check for Duplicates Help

I have a table that has an ssn column that is nullable. I want to allow duplicate null values but if someone tries to insert or update th column with a value that is not null I need to check to see if the value already exists and if so generate an error.

Can anyone tell or better yet provide an example of the best way to do this. I'm guessing a trigger but I'm pretty green when it comes to writing efficient triggers.

Thanx in advance!!!You can not use unique index but trigger will be good for your case:

drop table test
go
create table test(ssn varchar(7))
go
alter trigger no_duplicates on test
for insert,update
as
if exists(select ssn from test
where ssn in(select ssn from inserted)
and ssn is not null
group by ssn having count(*)>1)begin
raiserror('duplicates!!!',0,1)
rollback tran
end|||Thanks a bunch snail!!!|||No kidding?

DB2 has something SQL Server doesn't?

UNIQUE WHERE NOT NULL

Nothing like that here?

I'm shocked!|||that's an extension for fat ibm dba's

Check for column & insert

Can you tell me if this is possible? (and how to do it!!)

The application is VS2005, with sql database.

I want to check if a specific column exists in a specific table in the database and if not then add it, all via my application.

I'm happy knowing how to connect to the database & pass sql commands (as I'm doing that anyway to set off backups), but not the actual queries I'd need.

Hi,

Yes it is possible. This is an example (SQL 2005) that adds the column NewColumn to the Person.Contact table (AdventureWorks) if the column does not exists.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Person'
AND TABLE_NAME = 'Contact'
AND COLUMN_NAME = 'NewColumn')
BEGIN
ALTER TABLE Person.Contact
ADD NewColumn int
END

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Great thanks,

got that working now...