Query for column name and type

hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar
You could use sp_help.
Also consider INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.
See SQL Server 2000 Books Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar
|||Hi,
Execute the below query, replace the object_name with your object name
required.
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns
where table_name='object_name'
Tahnks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
> hi,
> can some one give me a query which retrieves column name ,
> and it's type ,by taking input as a table or view name..
> Sridhar
|||Thanks to you all...
>--Original Message--
>You could use sp_help.
>Also consider INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
>See SQL Server 2000 Books Online for more information.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:54b501c42d17$90dce570$a401280a@.phx.gbl...
>hi,
>can some one give me a query which retrieves column name ,
>and it's type ,by taking input as a table or view name..
>Sridhar
>
>.
>

Query for column name and type

hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
SridharYou could use sp_help.
Also consider INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.
See SQL Server 2000 Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar|||Hi,
Execute the below query, replace the object_name with your object name
required.
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns
where table_name='object_name'
Tahnks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
> hi,
> can some one give me a query which retrieves column name ,
> and it's type ,by taking input as a table or view name..
> Sridhar|||Thanks to you all...
>--Original Message--
>You could use sp_help.
>Also consider INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
>See SQL Server 2000 Books Online for more information.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:54b501c42d17$90dce570$a401280a@.phx.gbl...
>hi,
>can some one give me a query which retrieves column name ,
>and it's type ,by taking input as a table or view name..
>Sridhar
>
>.
>

Query for column name and type

hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
SridharYou could use sp_help.
Also consider INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS.
See SQL Server 2000 Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
hi,
can some one give me a query which retrieves column name ,
and it's type ,by taking input as a table or view name..
Sridhar|||Hi,
Execute the below query, replace the object_name with your object name
required.
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns
where table_name='object_name'
Tahnks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:54b501c42d17$90dce570$a401280a@.phx.gbl...
> hi,
> can some one give me a query which retrieves column name ,
> and it's type ,by taking input as a table or view name..
> Sridhar|||Thanks to you all...
>--Original Message--
>You could use sp_help.
>Also consider INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
>See SQL Server 2000 Books Online for more information.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:54b501c42d17$90dce570$a401280a@.phx.gbl...
>hi,
>can some one give me a query which retrieves column name ,
>and it's type ,by taking input as a table or view name..
>Sridhar
>
>.
>sql

Query Designer Automatically Expanding Select *

Ok this is a BIG annoyance. When I type Select * from table in one of the SQL
Query Tabs and then open the Query Designer it auto expands my query to show
all the fields. Example say my table has 30 fields when view it will expand
my query to read
select
field1,
field2,
field3,
etc....
from
table
is there any way to turn this feature off? I already think the query
designer has been severely hindered in this version of SQL Server because you
cant run the query in the designer.
Nope, I personally do agree with the approach, since select * is error prone
for a couple of reasons inside an application.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:9699BF30-86BC-473C-A9CA-B680EB5BF727@.microsoft.com...
> Ok this is a BIG annoyance. When I type Select * from table in one of the
> SQL
> Query Tabs and then open the Query Designer it auto expands my query to
> show
> all the fields. Example say my table has 30 fields when view it will
> expand
> my query to read
> select
> field1,
> field2,
> field3,
> etc....
> from
> table
> is there any way to turn this feature off? I already think the query
> designer has been severely hindered in this version of SQL Server because
> you
> cant run the query in the designer.
>
|||You are correct it can be error prone however my complaint is more about
something automatically changing something on me. I should be allowed to
turn the feature on or off.
"Gert E.R. Drapers" wrote:

> Nope, I personally do agree with the approach, since select * is error prone
> for a couple of reasons inside an application.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Jim" <Jim@.discussions.microsoft.com> wrote in message
> news:9699BF30-86BC-473C-A9CA-B680EB5BF727@.microsoft.com...
>
>

Query decimal points value in T-sql

I have one field with data type float contains data e.g 245.5,65,36.2..etc.
I
need to query out all the value with decimal points only, means those value
=
245.5,36.2..
How can I use transact sql to check?
ThanksOne way might be:
SELECT Col
FROM YourTable
WHERE (Col - CONVERT(INT, Col)) > 0
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"WS" <WS@.discussions.microsoft.com> wrote in message
news:49651063-1618-4DBD-975A-81DCD607E35F@.microsoft.com...
> I have one field with data type float contains data e.g
245.5,65,36.2..etc. I
> need to query out all the value with decimal points only, means those
value =
> 245.5,36.2..
> How can I use transact sql to check?
> Thanks

Query DB2 from SSRS and Convert Integer to Time

Hi,

I am querying DB2 from SSRS. I get an interger back that represents a time like this: HHMMSS

(The data type in DB2 is an integer.) I would like to make this representation of the time display a little more friendly. Does anyone have a good idea on how I can change the query or SSRS format to display semi-colons to break up the hours, minutes and seconds? Also, the other issue is that since I don't get leading zeros back....they probably need to be added to this value if the value is not a full 6 characters.

Here is one approach to this issue.

DECLARE @.MyTime int
SET @.MyTime = 63000

SELECT cast( stuff( stuff( right( '0' + cast( @.MyTime AS varchar(6) ), 6 ), 3, 0 , ':'), 6, 0, ':' ) AS datetime )

If this is a regular occurance, you may wish to create a FUNCTION for this task.

|||

Thanks. It looks like DB2 does not like the STUFF command, so I will try to apply this concept after I find a similar DB2 command.

Query Date Type - Somebody help me!p

Hi,

I have a table with the follow fields :

ID - Int
Date - Datetime

I need to make a simple query to result the records between to dates with a single ID.

Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230

Im using the follow :

ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and Final');

ADOQuery1.Parameters[0].Value:=Inicio;
ADOQuery1.Parameters[2].Value:=Final;

ADOQuery1.Open;

When I open the query it doesnt work cos its result a null set
How can I solve this?

Im using SQL Server 2000 and Delphi 6

Thanks for atention.where u have

Select * from Apro where data between Inicio and Final

should data be date ?? --> Date - Datetime
or u did that on purpose

Can u output the record source that is being executed ?

Query Date Type

Hi,

I have a table with the follow fields :

ID - Int
Date - Datetime

I need to make a simple query to result the records between to dates
with a single ID.

Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230

Im using the follow :

ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
Final');

ADOQuery1.Parameters[0].Value:=Inicio;
ADOQuery1.Parameters[2].Value:=Final;

ADOQuery1.Open;

When I open the query it doesnt work cos its result a null set
How can I solve this?

Im using SQL Server 2000 and Delphi 6

Thanks for atention.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Leonardo,

There are two problems in your code:

1. Parameters should start with a colon. (e.g. :Inicio and :Final)
2. You have used Parameters[0] and Parameters[2], what about Parameters[1]!?

So this is the correct code:

with ADOQuery1 do
begin
Close;
SQL.Text := 'Select * from Apro where data between Inicio and Final';
Parameters[0].Value := Inicio;
Parameters[1].Value := Final;
Open;
end;

Good luck,
Shervin

"Leonardo Almeida" <lfaa2004@.yahoo.com> wrote in message
news:3f8d396f$0$199$75868355@.news.frii.net...
>
> Hi,
> I have a table with the follow fields :
> ID - Int
> Date - Datetime
> I need to make a simple query to result the records between to dates
> with a single ID.
> Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230
> Im using the follow :
> ADOQuery1.Close;
> ADOQuery1.SQL.Clear;
> ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
> Final');
> ADOQuery1.Parameters[0].Value:=Inicio;
> ADOQuery1.Parameters[2].Value:=Final;
> ADOQuery1.Open;
> When I open the query it doesnt work cos its result a null set
> How can I solve this?
> Im using SQL Server 2000 and Delphi 6
> Thanks for atention.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Oops! Sorry I forgot to add colons before parameters :-) This is the correct
code:

with ADOQuery1 do
begin
Close;
SQL.Text := 'select * from Apro where data between :Inicio and :Final';
Parameters[0].Value := Inicio;
Parameters[1].Value := Final;
Open;
end;

Shervin

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:voquva8in6re84@.corp.supernews.com...
> Leonardo,
> There are two problems in your code:
> 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> 2. You have used Parameters[0] and Parameters[2], what about
Parameters[1]!?
> So this is the correct code:
> with ADOQuery1 do
> begin
> Close;
> SQL.Text := 'Select * from Apro where data between Inicio and Final';
> Parameters[0].Value := Inicio;
> Parameters[1].Value := Final;
> Open;
> end;
> Good luck,
> Shervin
>
> "Leonardo Almeida" <lfaa2004@.yahoo.com> wrote in message
> news:3f8d396f$0$199$75868355@.news.frii.net...
> > Hi,
> > I have a table with the follow fields :
> > ID - Int
> > Date - Datetime
> > I need to make a simple query to result the records between to dates
> > with a single ID.
> > Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230
> > Im using the follow :
> > ADOQuery1.Close;
> > ADOQuery1.SQL.Clear;
> > ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
> > Final');
> > ADOQuery1.Parameters[0].Value:=Inicio;
> > ADOQuery1.Parameters[2].Value:=Final;
> > ADOQuery1.Open;
> > When I open the query it doesnt work cos its result a null set
> > How can I solve this?
> > Im using SQL Server 2000 and Delphi 6
> > Thanks for atention.
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!|||Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> 2. You have used Parameters[0] and Parameters[2], what about
> Parameters[1]!?
> So this is the correct code:
> with ADOQuery1 do
> begin
> Close;
> SQL.Text := 'Select * from Apro where data between Inicio and Final';
> Parameters[0].Value := Inicio;
> Parameters[1].Value := Final;
> Open;
> end;

Shervin, did you not for get the colons?

Select * from Apro where data between :Inicio and :Final

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You are right, I forgot them. I fixed it.
Thanks Erland.

Shervin

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9415F34D56661Yazorman@.127.0.0.1...
> Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> > 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> > 2. You have used Parameters[0] and Parameters[2], what about
> > Parameters[1]!?
> > So this is the correct code:
> > with ADOQuery1 do
> > begin
> > Close;
> > SQL.Text := 'Select * from Apro where data between Inicio and Final';
> > Parameters[0].Value := Inicio;
> > Parameters[1].Value := Final;
> > Open;
> > end;
> Shervin, did you not for get the colons?
> Select * from Apro where data between :Inicio and :Final
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Query Data using Date

I using Windows XP SP2. Install SQL Server 2000 Developer Edition.
I want to query a table which contains a column of data type datetime using
ASP form, where the input of type text. Before querying I cahnge input text
into date format using CDate() function.
When I run the query I got this message:
"Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data
type to a datetime data type resulted in an out-of-range datetime value.
"
I have another Machine Installed with Windows 2000 Pro, SQL Server 2000
Developer Ed. When I ran the same query It works.
Wish to know what is the problem?Hi
Use YYYYMMDD format to deal with dates.
"wira659" <wira659@.discussions.microsoft.com> wrote in message
news:9E5FE92D-BA5B-45F4-B445-FF2DA189EC72@.microsoft.com...
> I using Windows XP SP2. Install SQL Server 2000 Developer Edition.
> I want to query a table which contains a column of data type datetime
using
> ASP form, where the input of type text. Before querying I cahnge input
text
> into date format using CDate() function.
> When I run the query I got this message:
> "Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char
data
> type to a datetime data type resulted in an out-of-range datetime value.
> "
> I have another Machine Installed with Windows 2000 Pro, SQL Server 2000
> Developer Ed. When I ran the same query It works.
> Wish to know what is the problem?

Query data type of column in DB table

Hi All

I want to retrieve the data type of table column and validate the input data whether same as data type of table column before insert into database. Any suggestion?

I use asp.net + msde

Thank you.

You could use TypedDataSets or TableAdapters to ensure your data type.|||

Thank you for your help.

Could I use both functions in web programming?

I searched both functions in MSDN and seems was used by visual studio.

Thank you.

|||Yes, Typed DataSets have been around since ASP.Net 1.1, and Table Adapters are new to ASP.Net 2.0. I use Table Adapters quite extensively now. They're really easy to use once you know how to set them up.|||

Thank you very much.

any web site I can read to know the syntax? Because I am a beginner of ASP.NET

THANKS!!

|||Here's a couple of links to a good tutorial on how to create a Data Access Layer and Business Logic Layer which includes usage of TableAdapters:

http://msdn.microsoft.com/asp.net/reference/data/default.aspx?pull=/library/en-us/dnaspnettut/html/aspnet_tutorial01_dataaccesslayer_cs.asp
http://msdn.microsoft.com/asp.net/reference/data/default.aspx?pull=/library/en-us/dnaspnettut/html/aspnet_tutorial02_businesslogiclayer_cs.asp|||

Thanks a lot.