Return all PKs in DB all tables

I'm trying to get all the PKs for all the tables in a DB. Right now I'm usi
ng:
sp_msforeachtable "print '?' exec sp_pkeys @.table_name = '?'"
However sp_msforeachtable returns the table names as [dbo].[tablename]. The
sp_pkeys proc takes tablename without dbo. or the brackets '[].'
Is there a better way to do this or what should I do to modify the script?
-PeterHi,
From the sysobjects tables you get the name of all primary keys. In the
criteria you can mention xtype = 'PK'
Select Name from sysobjects Where Xtype = 'PK'
For getting all the columns associated the primary key you could join
SYSOBJECTS table with INFORMATION_SCHEMA.KEY_COLUMN_USAGE
view
Thanks
Hari
SQL Server MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:04CD0D49-5925-4EB2-9D1B-5CCE728A5E75@.microsoft.com...
> I'm trying to get all the PKs for all the tables in a DB. Right now I'm
> using:
> sp_msforeachtable "print '?' exec sp_pkeys @.table_name = '?'"
> However sp_msforeachtable returns the table names as [dbo].[tablename].
> The
> sp_pkeys proc takes tablename without dbo. or the brackets '[].'
> Is there a better way to do this or what should I do to modify the script?
> --
> -Peter

Return a value using EXEC

OK, I'm fairly new to SQL Server, but I know SQL and databases pretty well. I'm just starting to use the dynamic SQL feature of SQL Server (with EXEC), and am wondering how to return a scalar value from a dynamic SQL expression. I realize I can't use EXEC in a user-defined function, but I want to create a stored procedure with one OUTPUT variable so I can simulate a function. The following code does not work, because EXEC does not return a value:

CREATE PROCEDURE dbo.ExecFunction ( @.ScalarSELECTString varchar(250), @.@.ReturnVal sql_variant )

set @.@.ReturnVal = ( exec @.ScalarSELECTString )

go

So, I was wondering if someone might be able to suggest a way to re-write the above code to achieve the same effect. Thanks in advance.First, Dynamic SQL should be a last resort option...

Do a google on SQL injection (security problem) and not to mention performance problems...

Second, who's in control of the statements? I'm assuming you are, so why not just create separate sprocs to do what you want? They'll be compiled, effecient, secure, ect...

You say you know db's and SQL...what's you're background?

OK, so now the answer to your question...is it depends on the SQL...

And you need a temp table. Also it seems like you're assuming that you'll always get only 1 row...if you get a set, you'll only store the last value...

But here's a sample...

USE Northwind
GO

CREATE PROC mySproc99 @.SQL varchar(4000), @.rs varchar(255) OUTPUT
AS
BEGIN
DECLARE @.cmd varchar(255)

CREATE TABLE #myTable99 (col1 varchar(8000))


SELECT @.cmd = 'INSERT INTO #myTable99(col1) ' + @.SQL
EXEC(@.cmd)

SELECT col1 FROM #myTable99
END
GO

DECLARE @.rs varchar(255)
EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @.rs
GO

DROP PROC mySproc99
GO|||Actually...I forgot the assignment...

USE Northwind
GO

CREATE PROC mySproc99 @.SQL varchar(4000), @.rs varchar(255) OUTPUT
AS
BEGIN
DECLARE @.cmd varchar(255)

CREATE TABLE #myTable99 (col1 varchar(8000))


SELECT @.cmd = 'INSERT INTO #myTable99(col1) ' + @.SQL
EXEC(@.cmd)

SELECT @.rs=col1 FROM #myTable99
END
GO

DECLARE @.rs varchar(255)
EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @.rs OUTPUT
SELECT RTRIM(@.rs)
GO

DROP PROC mySproc99
GO|||My DB background is mostly in Sybase SQLAnywhere... I never found a way to do dynamic SQL statements in that, so I usually ended up writing them into the front-end (VB.NET) rather than the DB itself; I just think this would be much more useful.

Security shouldn't be a problem for now, since there is no external access and we are a very small operation. As for the performance issue, I figured that might come up since it obviously can't pre-compile, but I wanted to have this as an option anyway so I don't have to repeat code.

I'm in control of the statements. Like I said, the idea is to create a generic tool that can be used repeatedly when a single value is needed.

As for creating separate sprocs... I assume "sprocs" are system procedures. How is this a better solution?

Originally posted by Brett Kaiser
First, Dynamic SQL should be a last resort option...

Do a google on SQL injection (security problem) and not to mention performance problems...

Second, who's in control of the statements? I'm assuming you are, so why not just create separate sprocs to do what you want? They'll be compiled, effecient, secure, ect...

You say you know db's and SQL...what's you're background?

OK, so now the answer to your question...is it depends on the SQL...

And you need a temp table. Also it seems like you're assuming that you'll always get only 1 row...if you get a set, you'll only store the last value...

But here's a sample...

USE Northwind
GO

CREATE PROC mySproc99 @.SQL varchar(4000), @.rs varchar(255) OUTPUT
AS
BEGIN
DECLARE @.cmd varchar(255)

CREATE TABLE #myTable99 (col1 varchar(8000))


SELECT @.cmd = 'INSERT INTO #myTable99(col1) ' + @.SQL
EXEC(@.cmd)

SELECT col1 FROM #myTable99
END
GO

DECLARE @.rs varchar(255)
EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @.rs
GO

DROP PROC mySproc99
GO|||Also, thanks for the solution anyway. I was hoping there would be some way to do it without a temp table, but I guess I'll go with that if I have to.

Originally posted by Brett Kaiser
First, Dynamic SQL should be a last resort option...

Do a google on SQL injection (security problem) and not to mention performance problems...

Second, who's in control of the statements? I'm assuming you are, so why not just create separate sprocs to do what you want? They'll be compiled, effecient, secure, ect...

You say you know db's and SQL...what's you're background?

OK, so now the answer to your question...is it depends on the SQL...

And you need a temp table. Also it seems like you're assuming that you'll always get only 1 row...if you get a set, you'll only store the last value...

But here's a sample...

USE Northwind
GO

CREATE PROC mySproc99 @.SQL varchar(4000), @.rs varchar(255) OUTPUT
AS
BEGIN
DECLARE @.cmd varchar(255)

CREATE TABLE #myTable99 (col1 varchar(8000))


SELECT @.cmd = 'INSERT INTO #myTable99(col1) ' + @.SQL
EXEC(@.cmd)

SELECT col1 FROM #myTable99
END
GO

DECLARE @.rs varchar(255)
EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @.rs
GO

DROP PROC mySproc99
GO|||SPROC just mean stored procedure...

And you're SQL statement would have to match the number of columns in the temp table...so I guess you'd also need dynamic sql to build the temp table to match...

seems like a lot of hoop kumping...

and what would you do when you get n rows back?|||The idea is that the SQL statement selects a single value, so I don't need to worry about multiple columns or multiple rows.

I will be creating a stored procedure to do this... that was the whole point from the beginning. However, I just discovered that I can't execute a stored procedure from within a function. It seems like the user-defined function capability is fairly limited in SQL Server as compared to SQLAnywhere. Is there any way to get around that?

Originally posted by Brett Kaiser
SPROC just mean stored procedure...

And you're SQL statement would have to match the number of columns in the temp table...so I guess you'd also need dynamic sql to build the temp table to match...

seems like a lot of hoop kumping...

and what would you do when you get n rows back?|||One other thing... in SQLAnywhere, to do performance tuning, all you have to do is turn on Profiling and then you can view the execution time of any procedure or function. I assume the equivalent is SQL Profiler, but how do I see the execution time of a specific function?|||Never mind, I found it in Query Analyzer.

Return a value from EXEC

Hi everybody

How to return a value like that
I dont want to use Stored proc.
Thanks

DECLARE @.cpt as integer
DECLARE @.pTable as varchar(40)

Select @.pTable = 'Salaires'
SELECT @.cpt = EXEC('Select Count(*) FROM ' + @.pTable)

Print @.cptdeclare @.i int
declare @.sql nvarchar(1000)
select @.sql = 'Select @.i = Count(*) FROM ' + @.pTable
exec sp_executesql @.sql, N'@.i int out', @.i out
select @.i

see
http://www.nigelrivett.net/sp_executeSQL.html

Return a value from EXEC

I have the following code:

DECLARE @.StartTimeStamp as datetime
DECLARE @.sqlcmd as varchar(8000)
DECLARE @.LogTable as varchar(20)
DECLARE @.PlantID as varchar(20)
DECLARE @.GroupIdent as varchar(20)
set @.LogTable = 'LOG_APPLE'
set @.PlantID = 'RETY'
set @.GroupIdent = '242'
BEGIN
SET @.sqlcmd = '
SET @.StartTimeStamp =
(SELECT TOP 1 timestamp
FROM ' + @.LogTable + '
WHERE timestamp <
(SELECT TPMSummaryProcessTime
FROM tblPRSGroup
WHERE PlantID = ''' + @.PlantID + ''' AND GroupIdent = ''' + @.GroupIdent + ''')
ORDER BY timestamp DESC)
'
SELECT @.sqlcmd
EXECUTE (@.sqlcmd)
SELECT @.StartTimeStamp
END

When I run it from Query Analyzer, I get the following message on the last SELECT statement:

Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.StartTimeStamp'.

If I copy the value of @.sqlcmd into Query Analyzer like the following with a DECLARE stament, I get the correct output:

DECLARE @.StartTimeStamp as datetime
SET @.StartTimeStamp =
(SELECT TOP 1 timestamp
FROM LOG_PX90J
WHERE timestamp <
(SELECT TPMSummaryProcessTime
FROM tblPRSGroup
WHERE PlantID = 'USFL' AND GroupIdent = '242')
ORDER BY timestamp DESC)
SELECT @.StartTimeStamp

This is the output I expect to receive:
----------------
NULL

(1 row(s) affected)

My question is, since the statement I am creating in @.sqlcmd is syntactically correct, how do I return the value from that command to @.StartTimeStamp via the EXECUTE statement?

P.S. I am forcing the variables to values just for testing purposes. I realize I should not be using "timestamp" as a field name also but I am dealing with legacy databases.

Thanks...This is because when you execute dynamic SQL the script is actually executed in a different process. As such any variables you declare do not exist when you call EXECUTE.

Look up the system stored proc sp_executesql in Books Online (BOL) to resolve this problem.

macka.|||Took your advise and did the following:

BEGIN
SET @.sqlcmd = N'
SET @.StartTimeStamp =
(SELECT TOP 1 timestamp
FROM @.LogTable
WHERE timestamp <
(SELECT TPMSummaryProcessTime
FROM tblPRSGroup
WHERE PlantID = @.PlantID AND GroupIdent = @.GroupIdent)
ORDER BY timestamp DESC)
'
SELECT @.sqlcmd
EXECUTE sp_executesql @.sqlcmd, N'@.StartTimeStamp datetime, @.LogTable varchar(20), @.PlantID varchar(20), @.GroupIdent varchar(20)',
@.StartTimeStamp, @.LogTable, @.PlantID, @.GroupIdent
SELECT @.StartTimeStamp
END

Now I get the following error:
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 21
Cannot convert parameter '@.statement' to ntext/nchar/nvarchar data type expected by procedure.|||I have been unable to test this as I don't have the table definitions or data, but give this a go.

DECLARE @.StartTimeStamp as datetime
DECLARE @.sqlcmd as nvarchar(4000)
DECLARE @.LogTable as varchar(20)
DECLARE @.PlantID as varchar(20)
DECLARE @.GroupIdent as varchar(20)
set @.LogTable = 'LOG_APPLE'
set @.PlantID = 'RETY'
set @.GroupIdent = '242'

SET @.sqlcmd = N'
SET @.StartTimeStamp =
(SELECT TOP 1 timestamp
FROM ' + @.LogTable + '
WHERE timestamp <
(SELECT TPMSummaryProcessTime
FROM tblPRSGroup
WHERE PlantID = ''' + @.PlantID + ''' AND GroupIdent = ''' + @.GroupIdent + ''')
ORDER BY timestamp DESC)
'

EXECUTE sp_executesql @.sqlcmd, N'@.StartTimeStamp datetime', @.StartTimeStamp

macka.|||MODIFY:

EXECUTE sp_executesql @.sqlcmd, N'@.StartTimeStamp datetime', @.StartTimeStamp
SELECT @.StartTimeStamp

TO:

EXECUTE sp_executesql @.sqlcmd, N'@.StartTimeStamp datetime', @.StartTimeStamp OUTPUT
SELECT @.StartTimeStamp

Enjoy your query:)|||Thanks for everyone's help. Your tips helped me solve the problem.sql

return a Scalar value...

How can I return a Scalar value by calling PROCEDURE 2 from PROCEDURE 1.
Something Like:
SET @.nextDataVersion = EXEC(pr_GetNextDataVersion 2)
(this does not work!)different EXEC (you're using the dynamic sql one)
should be:
EXEC @.nextDataVersion = pr_GetNextDataVersion 2
Note: This return value must be an int. [see RETURN in BOL for more]
Evan Camilleri wrote:
> How can I return a Scalar value by calling PROCEDURE 2 from PROCEDURE 1.
> Something Like:
> SET @.nextDataVersion = EXEC(pr_GetNextDataVersion 2)
> (this does not work!)
>|||Thanks! Quite stange syntax!
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:eVcKKWq9FHA.3880@.TK2MSFTNGP12.phx.gbl...
> different EXEC (you're using the dynamic sql one)
> should be:
> EXEC @.nextDataVersion = pr_GetNextDataVersion 2
> Note: This return value must be an int. [see RETURN in BOL for more]
> Evan Camilleri wrote:

Retrieving time zone adjustment

I am trying to use sp_help_targetserver to retrieve the time_zone_adjustment
from msdb on the local server. "EXEC sp_help_targetserver" returns the field
s
with no data. "EXEC sp_help_targetserver 'servername'" returns an error "The
specified @.server_name ('servername') does not exist.". I tried adding the
server group and the locsl server name by using sp_add_targetservergroup
(which itself said it was successful in both cases), but I get the same
results from sp_help_targetserver. What's am I missing?Hi
sp_help_targetserver is located in msdb and is a SQL Server Agent SP, used
for Master/Target Jobs.
It is not an information SP that can be used in the way you want to.
Regards
Mike
"Lauren" wrote:

> I am trying to use sp_help_targetserver to retrieve the time_zone_adjustme
nt
> from msdb on the local server. "EXEC sp_help_targetserver" returns the fie
lds
> with no data. "EXEC sp_help_targetserver 'servername'" returns an error "T
he
> specified @.server_name ('servername') does not exist.". I tried adding th
e
> server group and the locsl server name by using sp_add_targetservergroup
> (which itself said it was successful in both cases), but I get the same
> results from sp_help_targetserver. What's am I missing?
>|||Lauren wrote:
> I am trying to use sp_help_targetserver to retrieve the
> time_zone_adjustment from msdb on the local server. "EXEC
> sp_help_targetserver" returns the fields with no data. "EXEC
> sp_help_targetserver 'servername'" returns an error "The specified
> @.server_name ('servername') does not exist.". I tried adding the
> server group and the locsl server name by using
> sp_add_targetservergroup (which itself said it was successful in both
> cases), but I get the same results from sp_help_targetserver. What's
> am I missing?
You can probably get the time zone adjustment using:
select DATEDIFF(n, GETUTCDATE(), GETDATE())
David Gugick
Imceda Software
www.imceda.com|||Thanks. The getutcdate will get me what I need.
"David Gugick" wrote:

> Lauren wrote:
> You can probably get the time zone adjustment using:
> select DATEDIFF(n, GETUTCDATE(), GETDATE())
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>sql

Retrieve the version of SQL Server from within a user defined function

EXEC master.dbo.xp_msver ProductVersion can be used to return the server version in a resultset. I need this to do some conditional coding between varchar and varchar(max) in a UDF, so size of the text I return must be different between the SQL2000 and SQL2005.

I cant call an xp_ that returns a resultset within a UDF can I, so how can I get the SQL version?

have you tried

select serverproperty('ProductVersion') as character_value

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You can use @.@.Version within a user defined function. I tested the following code in both SQL 2000 and SQL 2005. Hope this helps.

Alter Function dbo.VersionNumber()

Returns int

As

Begin

Declare @.Temp VarChar(1000)

Declare @.Output Int

Select @.Temp = @.@.Version

Set @.Temp = Replace(Left(@.Temp, CharIndex('-', @.Temp)-1), 'Microsoft SQL Server', '')

If IsNumeric(@.Temp) =1

Set @.Output = Convert(int, @.Temp)

Else

Set @.Output = 0

Return @.Output

End

go

Select dbo.VersionNumber()

|||

serverproperty works very nicely, thanks.

I had tried @.@.VERSION, which worked, but not pleasant. Now changed to serverproperty