|
Can anyone tell me how to retrieve the column names in a table?
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
If you are using sql server then you can use the following query:
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'name_of_the_table'
|
|
|
|
|
ist working? i tried this query but its saying Invalid columnname...
Rajendran
|
|
|
|
|
Yes, it is. Just make sure you are performing the query against the database that contains your table
|
|
|
|
|
Thanks a lot. It is working fine.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
You are welcome 
|
|
|
|
|
hi
try this qry
select name from syscolumns where id=
(select id from sysobjects where name=)
|
|
|
|
|
In SQL,
Assume columnname- id(It should be identity and autoincrement),Name
then you will try this query
select name from tablename where id=0
Rajendran
|
|
|
|
|
In MS SQL Server syscolumns table holds column names and sysobjects table names.
SELECT * <br />
FROM syscolumns co<br />
INNER JOIN sysobjects so ON co.id = so.id<br />
WHERE so.name = 'mytable'
Regards
KP
|
|
|
|
|
Hi Jaya,
For a simple solution try this
sp_help ''
Senthil
|
|
|
|
|
|
sp_who/sp_who2 is not available in MySql. Use
SHOW FULL PROCESSLISTS , this shows which threads are running. Alternatively you can use MySql Administrator(comes with MySQL GUI Tools) to know the users and processes.
|
|
|
|
|
thank for help.it's work
|
|
|
|
|
hey Guys, i am having troubling with this query., i want to Assign the RefinanceAmount instead of LoanAmount if the LoanID exists in tblLoanPurpose(LoanID, RefinanceID, RefinanceAmount) which i joined the table.
the way i want is
L.LoanAmount = 'NewfundsAmt'+@RefinanceAmount
the whole Query is
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
Anyone pls Help..
ALTER PROC [dbo].[upExceptions_GetPending]
@UserID varchar(10)
AS
BEGIN
Declare @LoanAmount money
Declare @RefinanceAmount money
SELECT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'' [LoanIncrease], --tooltip
A.ShortName,
LS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
L.LoanAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
NULL [ExceptionDays],
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID
AND LS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID IN (1,2)
AND E.LoanIncreaseID IS NULL
UNION ALL
--UPFRONT LOAN INCREASES
SELECT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'Loan Increase' [LoanIncrease], --tooltip
A.ShortName,
LIS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
LI.IncreaseAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
NULL [ExceptionDays],
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID
AND LIS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID IN (1,2)
AND E.LoanIncreaseID IS NOT NULL
UNION ALL
--TRAIL LOANS
SELECT DISTINCT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'' [LoanIncrease], --tooltip
A.ShortName,
LS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
L.LoanAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
E.ExceptionDays,
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID
AND LS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID = 3
AND E.LoanIncreaseID IS NULL
AND CA.ApplicantTypeID = 1
UNION ALL
--TRAIL LOAN INCREASES
SELECT DISTINCT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'Loan Increase' [LoanIncrease], --tooltip
A.ShortName,
LIS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
LI.IncreaseAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
E.ExceptionDays,
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID
AND LIS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID = 3
AND E.LoanIncreaseID IS NOT NULL
AND CA.ApplicantTypeID = 1
ORDER BY TradingName, SettlementDate
--CRT.CommissionRunType, CR.CommissionRunDate, SettlementDate
END
|
|
|
|
|
Is this problem somewhat similar to this one [^]?
Do you need to add a prefix to L.LoanAmount?
[Please post an answer to my post not yours so I can get an e-mail notification when you do so.]
|
|
|
|
|
First of all thanks to your response.,
yes, i need to add the prefix, if the RefinanceAmount is exists., i.e Ref.Amt 10000.00 otherwise just display the LoanAmount'. Just to find out which amount is that.
Thanks Heaps
|
|
|
|
|
Do you mean something like this?
DECLARE @foo money
SET @foo = 10.02
SELECT 'PREFIX ' + CAST(@foo AS varchar(10))
|
|
|
|
|
Yeah, exactly thats all i want!! I tried it in different ways but couldn't succeed
Thanks a lot Buddy!!
|
|
|
|
|
|
I created a table in SQL Express with an autoinc column. When I remove the autoinc the table will create, but when I make the id column an auto increment it will not creawte the table.
Does anyone know why?
Is there aqnother way to auto increment with sql express?
Here is the table.
create table tbl_teachers (<br />
teach_id int AUTOINC NEXTVALUE 1 STEP 1,<br />
teach_fname text,<br />
teach_lname text,<br />
teach_add text,<br />
teach_unit_num text,<br />
teach_city text,<br />
teach_state text,<br />
teach_zip int,<br />
teach_instruments text,<br />
teach_email text,<br />
teach_phone int,<br />
teach_cell int,<br />
primary key(teach_id));
Here is the error I get.
Msg 102, Level 15, State 1, Line 3<br />
Incorrect syntax near 'AUTOINC'.
|
|
|
|
|
AUTOINC NEXTVALUE is Visual FoxPro syntax. IDENTITY is the SQL Server equivalent.
Paul Marfleet
|
|
|
|
|
OleDb connects fine?
VS2005; C#; SQL 2005 Developer (remote server)
SqlClient connection string:
"data source=MyRemoteServer;user id=MyUserID;password=MyPassword;initial catalog=DbName;workstation id=PC1"
OleDb connection string:
"provider=SQLxxxx;data source=MyRemoteServer;user id=MyUserID;password=MyPassword;initial catalog=DbName;workstation id=PC1"
(xxxx = I forget the exact provider string)
OleDb connects fine, SqlClient times out looking for the server?
Am I missing something obvious? Should I really worry about this - I thought SqlClient was faster and better?
Thanks
Ian
|
|
|
|
|
Did you get this to work?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Hi ,
All my fellow Iranians , Please Vote for this!
https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=307273
|
|
|
|
|
You should learn how to do a clickety link here on CP (it will keep people from having to copy the URL and paste in the address bar). Highlight the URL and press Ctrl+L 
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
