|
How to write Query to get the datatype of Fields or Columns of a table.
and How to write query to get all the table names in a database.
Thanks,
Preethi.
|
|
|
|
|
How to write query to get all the table names in a database.
Ans 1><code>select name as [Tablename] from sysobjects where xtype='u'</code>
How to write Query to get the datatype of Fields or Columns of a table
Ans 2> <code>SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName ORDER BY ORDINAL_POSITION</code>
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
Hi,
Getting table names from database is giving results. Thanks For Great support.
But, The next Query is giving problems Please look at this
i have a table name Customers in northwind, So i modified the Query which you given like this
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE <b>TABLE_NAME = Customers </b>ORDER BY ORDINAL_POSITION
But it is giving Error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Customers'.
Thanks,
Preethi
|
|
|
|
|
hey please look
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers' ORDER BY ORDINAL_POSITION
OR
declare @tablename varchar(20)
set @tablename = 'Customers'
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename ORDER BY ORDINAL_POSITION
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
Hi ,
I applied
declare @tablename varchar(20)
set @tablename = 'Customers'
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'Customers' ORDER BY ORDINAL_POSITION
But still iam getting Error in execution
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '='.
Thanks Swati for your Kind support.
|
|
|
|
|
Preethi in your query there is error please put space between
INFORMATION_SCHEMA.COLUMNSWHERE
thats why you getting error of '=' sign
declare @tablename varchar(20)
set @tablename = 'Customers'
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = tablename ORDER BY ORDINAL_POSITION
Reasons are not Important but Results are Important.
Swati Tripathi
|
|
|
|
|
Thanks Swati,
Iam feeling shame to not caught this Synax error.
Once again Thanks for support.
Bye,
Preethi
|
|
|
|
|
I would assume you have SQL 2005, look into the information schema views, in management studio under views|system views
If you are in an earlier version you are going to have to make them up using sysobjects and syscolumns.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
iam using Sql server 2000.
|
|
|
|
|
Hi
I have result set as
table name=ID
ID1 ID2 ID3
1 3 1
2 6 1
1 1 1
I want output as
Name1 Name2 Name3
A C A
B D A
A A A
I have one table for name is Name field of this table are:Id,Name.
I m trying query as
Select Name.Name as Name1 Name ,Name.Name as Name2 from Name,Name.Name as Name3
from Name
inner join ID on Name.ID=ID1
Inner join ID on Name.ID=ID2
inner join ID on Name.ID=ID3
above query is giving error. Plz help in this
thanx
Care Carrer
|
|
|
|
|
Man thats ugly, got to be one on the lousiest data structures around.
Joins are not going to do it. You will need to use sub selects.
select <br />
(Select name from name where ID = ID1) name1,<br />
(Select name from name where ID = ID2) name2, <br />
(Select name from name where ID = ID3) name3<br />
from ID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i have a table with thousands of records. i have to change the status of the record if 3 new records entered in the table. for eg if i have 4 records in the table i shoud change the first record and for the second record status change, i should have three more new records except already existing records. Please help
Thank You!
|
|
|
|
|
Need more info but just from the sound of it you should be using 1 record with nested records in XML using XML data type. If you are using a queue you are using xml for the conversation.
|
|
|
|
|
What SQL language? Can make SQL Server downtime (Dead, paralysis)
I was only joking and friends
|
|
|
|
|
ChinaTJBoy wrote: I was only joking and friends
Ohh!!! I thought that some Chinese is practicing English. 
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
So a Rabbi, a Monk and a Priest walk into a bar and the bartender says "What is this some kind of joke?"
//I am available for birthday party's and barmitzfas....
|
|
|
|
|
i think this is not right place for sharing your jokes...
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
ChinaTJBoy wrote: I was only joking
You need to really work on your joke telling. They are pretty off 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Big Joke
♫ 99 little bugs in the code,
99 bugs in the code
We fix a bug, compile it again
101 little bugs in the code ♫
|
|
|
|
|
Hi all,
I want to execute a union query where data comes from two different servers.What i did is first i linked the server to my current server as
Exec sp_addlinkedServer 'myserver'
Exec sp_addlinkedsrvlogin 'myserver','false',NULL,sa,''
and
then i executed the foll query
select user_name+'t1' from myserver.mydatabase.dbo.User
but i get the foll error
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.
any help would be appreciated...
When you fail to plan, you are planning to fail.
|
|
|
|
|
I'm guessing that the remote server does not have Mixed Mode Authentication enabled.
SG
Aham Brahmasmi!
|
|
|
|
|
hi,
go through this
http:
and then also if your problem occurs then
"This is caused by trying to connect to SQL Server when SQL Server is not configured for Mixed Mode authentication."
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
Hi thanks for your reply...
The link u provided really helped..
Thanks again.
When you fail to plan, you are planning to fail.
|
|
|
|
|
Is there possibility to define unsigned type or convert already exist type, int for example, to unsigned int in MSSQL?
|
|
|
|
|
Can you be more clear in your question?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
