Retrieving constraint information

Hello

I wrote this query to retrieve all constraints (primary keys, foreign keys, unique keys, checks) on a table.


SELECT
c.name AS name,
CASE
WHEN c.xtype = 'PK' THEN 'primary'
WHEN c.xtype = 'F' THEN 'foreign'
WHEN c.xtype = 'UQ' THEN 'unique'
WHEN c.xtype = 'C' THEN 'check'
END AS type,
tkt.name AS contable,
tkc.name AS confield,
fkt.name AS reftable,
fkc.name AS reffield,
com.text AS expr
FROM
sysobjects c
LEFT JOIN sysconstraints con ON con.constid = c.id
LEFT JOIN sysforeignkeys fks ON fks.constid = con.constid
LEFT JOIN sysobjects tkt ON tkt.id = con.id
LEFT JOIN syscolumns tkc ON tkc.id = tkt.id AND tkc.colid = con.colid
LEFT JOIN sysobjects fkt ON fkt.id = fks.rkeyid
LEFT JOIN syscolumns fkc ON fkc.id = fkt.id AND fkc.colid = fks.rkey
LEFT JOIN syscomments com ON com.id = c.id
WHERE
c.xtype IN ('PK', 'F', 'UQ', 'C')
AND tkt.name = '$table'
AND c.name = '$constraint'



It returns a row for each constraint which can be easilly stored in an associative array.


Array (
[name], //name of the constraint
[type], //(primary|foreign|unique|check)
[contable], //table the constraint is on
[confield], //field the constraint is on
[reftable], //referenced table, null if type!=foreign
[reffield], //referenced field, null if type!=foreign
[expr], //check expression, null if type!=check
)


Everything works as expected except for one issue. For primary keys and unique keys, the sysconstraints.colid field is always '0'. The sysconstraints.id field properly indicates the id of the table that the primary/unique key is on, however I have no way of knowing which column(s) the primary/unique key is on. According to the Transact-SQL reference, the sysconstraints.colid field is the "ID of the column on which the constraint is defined, 0 if a table constraint.". Therefore, it looks like primary/unique constraints are stored as table constraints instead as a primary/unique constraint. However the sysconstraints.status field indicates the type of constraint to be a primary constraint or a unique constraint, not a table constraint.

Pseudo-bit-mask indicating the status. Possible values include:

1 = PRIMARY KEY constraint.
2 = UNIQUE KEY constraint.
3 = FOREIGN KEY constraint.
4 = CHECK constraint.
5 = DEFAULT constraint.
16 = Column-level constraint.
32 = Table-level constraint.

Is there something I am missing? Or maybe there is a better way to find the columns of a primary key or unique key that I can integrate into my above query?

Thanks

-except10nUse INFORMATION_SCHEMA instead. I have done almost the same what you are trying to do and late I understood that this is not the best way to use internal tables. Every SP can change something and you will have a problem.|||Use INFORMATION_SCHEMA instead. I have done almost the same what you are trying to do and late I understood that this is not the best way to use internal tables. Every SP can change something and you will have a problem.
Unfortunately I can't use INFORMATION_SCHEMA for reasons that would take too long to explain at the moment. In your past experiences, were you able to achieve what I am trying to do using the standard system tables?

retrieve data from a stored procedure

Hello Group
I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can't retrieve the data. Here is my code.

CREATE PROCEDURE stpMyAuthentication
(
@.fldUsername varchar( 50 ),
@.fldPassword Char( 25 )--,
--@.fldFirstName char( 30 ) OUTPUT,
--@.fldLastName char( 30 ) OUTPUT
)
As
DECLARE @.actualPassword Char( 25 )
SELECT
@.actualPassword = fldPassword
FROM
tbMembUsers
Where
fldUsername = @.fldUsername
IF @.actualPassword IS NOT NULL
IF @.fldPassword = @.actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
SELECT
fldFirstName,
fldLastName,
fldPassword
FROM
tbMembUsers
Where
fldUsername = @.fldUsername
GO

############### login page ################

 Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
 If IsValid Then
 If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
 FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
 End If
 End If
 End Sub
 Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
 Dim myConn As SqlConnection
 Dim myCmd As SqlCommand
 Dim myReturn As SqlParameter
 Dim intResult As Integer
 Dim sqlConn As String
 Dim strFirstName, strLastName As String
 sqlConn = ConfigurationSettings.AppSettings("sqlConnStr")
 myConn = New SqlConnection(sqlConn)
 myCmd = New SqlCommand("stpMyAuthentication", myConn)
 myCmd.CommandType = CommandType.StoredProcedure
 myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
 myReturn.Direction = ParameterDirection.ReturnValue
 myCmd.Parameters.Add(Trim("@.fldUsername"), Trim(strUsername))
 myCmd.Parameters.Add(Trim("@.fldPassword"), Trim(strPassword))
 myCmd.Parameters.Add("@.fldFirstName", strFirstName)
 myCmd.Parameters.Add("@.fldLastName", strLastName)
 myCmd.Parameters.Add("@.fldPassword", strPassword)
 myConn.Open()
 myCmd.ExecuteNonQuery()
 intResult = myCmd.Parameters("RETURN_VALUE").Value
 myConn.Close()
 'If strPassword = 55555555 Then
 ' Session("intDefaultPass") = 1
 'End If
 Session("strFullName") = strFirstName & " " & strLastName
 Session("strPassword") = strPassword
 If intResult < 0 Then
 If intResult = -1 Then
 lblMessage.Text = "Username Not Registered!<br><br>"
 Else
 lblMessage.Text = "Invalid Password!<br><br>"
 End If
 End If
 Return intResult
 End Function

At this time I am not getting any errors. How can I retrieve the data that I am after?
Michaeldude
your code is all screwed up. re-work on your logic. you dont need to select the actualpassword in a sep variable and compare it against whats in the variable. you can directly compare it against the value in the table.

i'd would rewrite your query as :

CREATE PROCEDURE stpMyAuthentication
(
@.fldUsername varchar( 50 ),
@.fldPassword varChar( 25 ),
@.fldFirstName varchar(30) OUTPUT,
@.fldLastName varchar( 30 ) OUTPUT
@.intRes int OUTPUT
)
As
SET NOCOUNT OFF
BEGIN
--@.intRes 0=no user. 1=valid. 2=invalid passwd
IF EXISTS ( Select * from tbMembUsers where fldUsername = @.fldUsername and fldPassword=@.fldPassword )
BEGIN
SELECT @.fldFirstName=fldFirstName, @.fldLastName =fldLastName
FROMtbMembUsers
WHEREfldUsername = @.fldUsername AND fldPassword=@.fldPassword
SET@.intRes=1
END
ELSE
BEGIN
-- INVALID PASSWORD
IF EXISTS ( Select * from tbMembUsers where fldUsername = @.fldUsername )
SET @.intRes=2
--No CREDENTIALS FOUND SO RETURN 0
IF EXISTS ( select * From tbMembUsers where fldUsername = @.fldUsername and fldPassword=@.fldPassword )
SET @.intRes=0
END
SET NOCOUNT OFF
END

and from the asp.net page you can use the sample code here to modify it acc to your stored proc.

Dim myCommand As SqlCommand
Dim myParam As SqlParameter
dim resvalue as integer
myCommand = New SqlCommand()
myCommand.Connection = objcon
myCommand.CommandText = "testmovein_cusinfo"
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@.username",SqlDbType.varchar,30))
myCommand.Parameters("@.username").Value = Trim(username)
'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.int
mycommand.Parameters.Add(myParam)
Try
If objCon.State = 0 Then objCon.Open()
mycommand.ExecuteNonQuery()
resvalue=convert.toint16((mycommand.Parameters("@.result").Value))
Catch exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
 objCon.Close()
End If
'objCon.Dispose()
End Try

hth|||why do you SET NOCOUNT OFF twice?

and i think you'll confuse him with
'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.int
mycommand.Parameters.Add(myParam)
----

mycommand.Paramters.Add("@.Result", SqlDbType.int)
mycommand.Parameters("@.Result").Direction = ParameterDirection.Output

does the same thing. :)|||yeah sorry abt the nocount thing.

theres always more than one way you can do a thing in. i just cut n pasted some template code.|||Thanks guys. I finally got it. Even though this code works just fine, I am wondering if I have some unnecessary code. Can you please check it for me thanks.

 Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
 ' Variable Declaration
 Dim myConn As SqlConnection
 Dim myCmd As SqlCommand
 Dim myParam As SqlParameter
 Dim intResult As Integer
 Dim sqlConn As String
 Dim strFirstName, strLastName As String
 Dim resvalue As Integer
 sqlConn = ConfigurationSettings.AppSettings("sqlDbConn")
 myConn = New SqlConnection(sqlConn)
 myCmd = New SqlCommand("stpMyAuthentication", myConn)
 myCmd.CommandType = CommandType.StoredProcedure
 'output parameter
 myParam = myCmd.CreateParameter()
 myParam.Direction = ParameterDirection.Output
 myParam.SqlDbType = SqlDbType.Int
 myCmd.Parameters.Add(myParam)
 '########################################################
 myCmd = New SqlCommand
 myCmd.Connection = myConn
 myCmd.CommandText = "stpMyAuthentication"
 myCmd.CommandType = CommandType.StoredProcedure
 myCmd.Parameters.Add("@.intRes", SqlDbType.Int)
 myCmd.Parameters("@.intRes").Direction = ParameterDirection.Output
 myCmd.Parameters.Add(Trim("@.fldUsername"), Trim(strUsername))
 myCmd.Parameters.Add(Trim("@.fldPassword"), Trim(strPassword))
 myCmd.Parameters.Add("@.fldFirstName", SqlDbType.VarChar, 30)
 myCmd.Parameters("@.fldFirstName").Direction = ParameterDirection.Output
 myCmd.Parameters.Add("@.fldLastName", SqlDbType.VarChar, 30)
 myCmd.Parameters("@.fldLastName").Direction = ParameterDirection.Output
 '########################################################
 Try
 Response.Write("1<br>")
 If myConn.State = 0 Then myConn.Open()
 myCmd.ExecuteNonQuery()
 resvalue = Convert.ToInt16((myCmd.Parameters("@.intRes").Value))
 strFirstName = myCmd.Parameters("@.fldFirstName").Value
 strLastName = myCmd.Parameters("@.fldLastName").Value
 Session("strFullName") = strFirstName & " " & strLastName
 Catch exc As Exception
 Response.Write(exc)
 Finally
 If myConn.State = ConnectionState.Open Then
 myConn.Close()
 End If
 'objCon.Dispose()
 End Try
 Select Case resvalue
 Case 0
 lblMessage.Text = "Username Not Registered!<br><br>"
 Case 1
 If strPassword = "55555555" Then
 Session("intDefaultPass") = 1
 End If
 Case 2
 lblMessage.Text = "Invalid Password!<br><br>"
 End Select
 Return resvalue
 End Function