How to format in SQL

Hi All,

I have a serial number field in table. Field type is integer. It is just stored as 1,2,3,12,13, etc.

It is showing as 00001,00002,00003,00012,00013 in interface. C# string format is very easy to changed the format.

But when i export to excel there is a problem. Let me know how to format string in SQL and export to excel.

Thanks

Aung

Hi Aung,

You may want to try this

select right('00000'+cast(serialno as varchar(5)),5) from table

|||

select right('0000'+cast(serialno as varchar(5)),5) from table

should work.

Or you can use:

SELECTRIGHT('0000'+CONVERT(VARCHAR(5),serialno),5) FROM yourTable

|||

Thanks BRO...

This is what I want.

How to format a date field in select query

Is it possible to format the date field create_date (mm/dd/yyyy or mm/dd/yy)
I use the following query in stored proc. will be called in the asp.net page for population the datagrid.

select id, name, create_date from actionstable;

Please help, Thank you.You can use the SQL CONVERT() function, to convert the date to an nvarchar(), or better, format the date in the presentation layer, in the datagrid itself. In the DataFormatString of the DataGrid's column that will contain the date, use 0:d

How to force CRUDs be handled through Stored Proc.

Hi,
If I want no one to be able to use then native select, delete, update etc..
and rather force the user to use stored procedure that I have included in th
e
server. how to do that?
Give:
the database has 2- accounts...one limited privileges account for the users
to use and one for me the owner. I want to deny usage of stored procedures.
plus I want to encrypt the procedure listing so now one can see the inside
Thank youDon't give the users permissions directly on the tables, only the stored pro
cedures. As for
encryption, create the procs using WITH ENCRYPTION (however, if someone want
s to, they can Google
for decryption and find it within a minute).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:76285543-FE12-4D61-80F9-E07A6BFDEABD@.microsoft.com...
> Hi,
> If I want no one to be able to use then native select, delete, update etc.
.
> and rather force the user to use stored procedure that I have included in
the
> server. how to do that?
> Give:
> the database has 2- accounts...one limited privileges account for the user
s
> to use and one for me the owner. I want to deny usage of stored procedures
.
> plus I want to encrypt the procedure listing so now one can see the inside
> Thank you|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edPqteKSFHA.3788@.tk2msftngp13.phx.gbl...
> Don't give the users permissions directly on the tables, only the stored
> procedures. As for encryption, create the procs using WITH ENCRYPTION
> (however, if someone wants to, they can Google for decryption and find it
> within a minute).
Yeah, WITH ENCRYPTION tends to keep "honest people honest"|||Then how do i protect my stored procedures for listing?
"Michael C#" wrote:

> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:edPqteKSFHA.3788@.tk2msftngp13.phx.gbl...
> Yeah, WITH ENCRYPTION tends to keep "honest people honest"
>
>|||You can't.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:2505B075-0207-48EB-BA08-87C57FE3088D@.microsoft.com...
> Then how do i protect my stored procedures for listing?
> "Michael C#" wrote:
>|||Thank you all for the help,
I want to highlight that the user account is secret as well. the users must
use an application to access the db. the application has an obfuscated user
account and password that is authorized to do cruds through stored procedure
s
(assuming I denied direct access to the table) so this user account is the
only way a user can access the db. For a user to log to the SQl sever he mus
t
guess the account and password. which as securely saved/protected PKI model.
But assume he guessed the account (limited privileges) and password, and he
is now on the server. he will not be able to use the stored procedures
because I designed the procedures to take a parameter that is secret and
saved again within the application that user must use to access the
database... so the user can see the stored procedure signature I presume bu
t
have to guess the key. he is not the owner so he cant delete, and it was
saved WITH ENCRYPTION so it's encrypted and he can't see the listing and
hence see the" IF ELSE" where I check for the secret key value passed to the
procedure. Now the nightmare is that he decrypts the stored procedure...so
I have 3 questions
1- How can I protect him from opening the stored procedures?
2- Can I program the stored procedure to include check such if else
etc,,(obviously I'm novice to T-Sql)
3- can I use the e-mail mechanism from within a stored procedure to notify
me of suspicious attempts. such as when the key entered was bad. based on my
closed model on failed key attempt is too many and would trigger a
notification email.
Thank you so very much
"Tibor Karaszi" wrote:

> Don't give the users permissions directly on the tables, only the stored p
rocedures. As for
> encryption, create the procs using WITH ENCRYPTION (however, if someone wa
nts to, they can Google
> for decryption and find it within a minute).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote i
n message
> news:76285543-FE12-4D61-80F9-E07A6BFDEABD@.microsoft.com...
>
>|||1. You can't.
2. Yes. There are procedural constructs in TSQL. See for instance IF..ELSE i
n Books Online.
3. You could use xp_sendmail or xp_smtp_sendmail (better, doesn't use MAPI,
but you need to download
and install from www.sqldev.net). Or put enough info in a table and have an
outside process (like
SQL Server Agent job) regularly read this table and send emails. Or use Noti
fication Services (free
download from MS).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:CC563474-6A2B-4792-86DC-5AE5CFC0F352@.microsoft.com...
> Thank you all for the help,
> I want to highlight that the user account is secret as well. the users mus
t
> use an application to access the db. the application has an obfuscated use
r
> account and password that is authorized to do cruds through stored procedu
res
> (assuming I denied direct access to the table) so this user account is the
> only way a user can access the db. For a user to log to the SQl sever he m
ust
> guess the account and password. which as securely saved/protected PKI mode
l.
> But assume he guessed the account (limited privileges) and password, and h
e
> is now on the server. he will not be able to use the stored procedures
> because I designed the procedures to take a parameter that is secret and
> saved again within the application that user must use to access the
> database... so the user can see the stored procedure signature I presume
but
> have to guess the key. he is not the owner so he cant delete, and it was
> saved WITH ENCRYPTION so it's encrypted and he can't see the listing and
> hence see the" IF ELSE" where I check for the secret key value passed to t
he
> procedure. Now the nightmare is that he decrypts the stored procedure...so
> I have 3 questions
> 1- How can I protect him from opening the stored procedures?
> 2- Can I program the stored procedure to include check such if else
> etc,,(obviously I'm novice to T-Sql)
> 3- can I use the e-mail mechanism from within a stored procedure to notify
> me of suspicious attempts. such as when the key entered was bad. based on
my
> closed model on failed key attempt is too many and would trigger a
> notification email.
> Thank you so very much
> "Tibor Karaszi" wrote:
>|||If i cant prevemt decrymption, then my only lien of defence the is embeded
acount and password. and hope the user will never abe able to guess.
Do you have better suggestions?
"Tibor Karaszi" wrote:

> 1. You can't.
> 2. Yes. There are procedural constructs in TSQL. See for instance IF..ELSE
in Books Online.
> 3. You could use xp_sendmail or xp_smtp_sendmail (better, doesn't use MAPI
, but you need to download
> and install from www.sqldev.net). Or put enough info in a table and have a
n outside process (like
> SQL Server Agent job) regularly read this table and send emails. Or use No
tification Services (free
> download from MS).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote i
n message
> news:CC563474-6A2B-4792-86DC-5AE5CFC0F352@.microsoft.com...
>
>|||> 1- How can I protect him from opening the stored procedures?
You can't easily. The better way to get the level of security you are reques
ting
here would be to encrypt the actual data and have the stored procs merely
provide CRUD services. Thus, even if the user sees the stored proc, without
the
ability to decrypt the data itself, the stored proc by itself would useless.

> 2- Can I program the stored procedure to include check such if else
> etc,,(obviously I'm novice to T-Sql)
Not sure what you mean here.

> 3- can I use the e-mail mechanism from within a stored procedure to notify
> me of suspicious attempts. such as when the key entered was bad. based on
my
> closed model on failed key attempt is too many and would trigger a
> notification email.
> Thank you so very much
Yes but it might be trickier than you think. This can be done in the stored
procs themselves and/or in your middle layer code.
Thomas|||"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:7EB4CFB4-BCC6-4B7D-9204-B17B16C916E6@.microsoft.com...
> If i cant prevemt decrymption, then my only lien of defence the is embeded
> acount and password. and hope the user will never abe able to guess.
> Do you have better suggestions?
WITH ENCRYPTION keeps "honest people honest", and prevents novices from
hacking into your code, and it's not all that secure. There are just too
many tools available to decrypt SP's. Another idea might be to store your
queries internally to your application in an encrypted format and decrypt
right before execution, instead of using SP's. You'll take a performance
hit on this, however, which may or may not be negligible. This brings you
back full-circle to your original question, however, about forcing access to
tables only via SP's...sql

How to Force all Stored Procedures to "recompile"?

Is there a way to force all stored procedures to "recompile" or create a new
execution plan? What we are trying to do is find a way to quickly identify
all stored procedures that are invalid because of schema changes like table
s or columns dropped or alt
ered.
Thanks,
BLGYou are asking two different things. AFAIK, there's no way to know which pla
ns are invalidated (a plan can be
invalidated for several reasons).
If you want a proc to recompile at next execution, you can use sp_recompile
on any of the tables that the proc
is using.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"blg" <anonymous@.discussions.microsoft.com> wrote in message
news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> Is there a way to force all stored procedures to "recompile" or create a new execu
tion plan? What we are
trying to do is find a way to quickly identify all stored procedures that ar
e invalid because of schema
changes like tables or columns dropped or altered.
> Thanks,
> BLG|||Hi,
Run the command DBCC FREEPROCCACHE to remove all compile plans from the
procedure cache.
Karl Gram, BSc, MBA
http://www.gramonline.com
"blg" <anonymous@.discussions.microsoft.com> wrote in message
news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> Is there a way to force all stored procedures to "recompile" or create a
new execution plan? What we are trying to do is find a way to quickly
identify all stored procedures that are invalid because of schema changes
like tables or columns dropped or altered.
> Thanks,
> BLG|||D'oh. Why didn't I think of that? :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message news:uqRIwlqEEHA.4080@.TK2MSFTNGP09.
phx.gbl...
> Hi,
> Run the command DBCC FREEPROCCACHE to remove all compile plans from the
> procedure cache.
> --
> Karl Gram, BSc, MBA
> http://www.gramonline.com
>
> "blg" <anonymous@.discussions.microsoft.com> wrote in message
> news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> new execution plan? What we are trying to do is find a way to quickly
> identify all stored procedures that are invalid because of schema changes
> like tables or columns dropped or altered.
>

How to force a recompile on the stored procs?

I want to recompile all the stored procs in the database. I've tried
sp_recompile on all of them, but it simply marks them to be recompiled.
ThanksThey don't actually get recompiled until the next time they get called.
That is how it always works.
--
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks|||DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks

How to force a recompile on the stored procs?

I want to recompile all the stored procs in the database. I've tried
sp_recompile on all of them, but it simply marks them to be recompiled.
Thanks
They don't actually get recompiled until the next time they get called.
That is how it always works.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks
|||DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks

How to force a recompile on the stored procs?

I want to recompile all the stored procs in the database. I've tried
sp_recompile on all of them, but it simply marks them to be recompiled.
ThanksThey don't actually get recompiled until the next time they get called.
That is how it always works.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks|||DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks

how to force a commit in a sp

I've a complex stored procedure, that makes a lot of insert, update,
delete and so on.

I would like to make some commits durint this sp, but of course they
are not "real" commit because who call the sp could decide for a
rollback.

But I know that this commit has to be real. In fact, the transaction
log grows really too much during the execution.

Is there a way to force a commit durint a sp ?

thank you very much!Alberto (iltrex@.libero.it) writes:
> I've a complex stored procedure, that makes a lot of insert, update,
> delete and so on.
> I would like to make some commits durint this sp, but of course they
> are not "real" commit because who call the sp could decide for a
> rollback.
> But I know that this commit has to be real. In fact, the transaction
> log grows really too much during the execution.
> Is there a way to force a commit durint a sp ?

WHILE @.@.trancount > 1
COMMIT TRANSACTION

But it would be a really bad thing to do. If the caller has started a
trasaction, he would get an error when you exit the procedure. (Unless
you are so deceivious that perform equally many BEGIN TRANSACTION.

A much better approach is to add to the beginning of the procedure:

IF @.@.trancount > 0
BEGIN
RAISERROR ('This procedure must not be called within a transaction',
16, 1)
RETURN 1
END

That assumes of course that there are no business requirements that
calls for your procedure being part of a transaction. If there is,
you will have to find other ways to address the transaction log growth.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> WHILE @.@.trancount > 1
> COMMIT TRANSACTION
> But it would be a really bad thing to do. If the caller has started a

I know. But the sp calculates data for a olap cube, and it does the
calculation in an incremental way (it can be interrupted at any time
without losing data). So you solution should be the one I'm looking
for. Now I'm going to try it!

thank you!|||Alberto (iltrex@.libero.it) writes:
>> WHILE @.@.trancount > 1
>> COMMIT TRANSACTION
>>
>> But it would be a really bad thing to do. If the caller has started a
> I know. But the sp calculates data for a olap cube, and it does the
> calculation in an incremental way (it can be interrupted at any time
> without losing data). So you solution should be the one I'm looking
> for. Now I'm going to try it!

Yeah, but the caller might have done something which cannot be
committed half-way. So I really recommend the other way:

IF @.@.trancount > 0
BEGIN
RAISERROR ('This procedure must not be called within a transaction',
16, 1)
RETURN 1
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

How to flush the buffer to trc file

I want to trace the user logins by using a stored procedure. This script (sp_login_trace) is created by the SQL Profiler tool. (Once this procedure works well, I will use sp_procoption to run it automatically everytime the SQL Server startup.)

After I successfully created sp_login_trace, I run it (exec sp_login_trace). The trace process is started and TraceID is 1. (I use select * from ::fn_trace_getinfo(default) to verify it). However the file size of login_trace.trc is always 0 even after I use Query Ananlysis or Eneterprise manager to let some users to login into the SQL Server instance. (when I use SQL Profiler to start a trace, the trace file size will increase along with users continaully login in). At that time if I use SQL Profiler to open the login_trace.trc file, the system will give me an error message: No data since Empty File.

After I stop and delete the trace process, I find that the file size of login_trace.trc becomes 128K and I can see the login records caught by sp_login_trace if I use SQL Profiler to open this file again.

How can I flush the buffer to trc file frequently without need of stopping trace process?

Thanks for helps in advance.

LeonHere is the script of sp_login_trace (Leon)

CREATE PROCEDURE sp_login_trace
AS
BEGIN
/************************************************** **/
/* The following statements contain the SQL Server Profiler-generated */
/* script to create the trace with the required events and data columns. */
/************************************************** **/
-- Create a queue
DECLARE @.rc int
DECLARE @.TraceID int
DECLARE @.maxfilesize bigint
SET @.maxfilesize = 5
EXEC @.rc = sp_trace_create @.TraceID output, 2,
N'C:\Allprojects\SQL_Server_2000\login_trace',
@.maxfilesize, NULL
IF(@.rc != 0) GOTO error

-- You can't script the client-side file and table.
-- Set the events.
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on

-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint

exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'

-- Set the trace status to start.
EXEC sp_trace_setstatus @.TraceID, 1

GOTO finish

error:
SELECT ErrorCode=@.rc

finish:
END

GOsql

how to fine a hole in a records?

Hi all!

I need your help to realize algorithm for stored proc or trigger.

tool: MS SQL server 2000, T-SQL

TABLE:
[unique_id] [mynumber] [week]

[unique_id] - bigint,primary key, identity auto-increnment
[week] - int, 1-53, week number
[mynumber] - int, 1 - 7, for every week, daily record one per day, up
to 7 per week

so, for every week we have a mynumber from 1 to 7
or nothing (if no records for that day),

we can insert or delete mynubers in any order, at will

EXAMPLE:

week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value
= 4
week 2, mynumber 1,2,3,5,7 - so next mynumber = 4

QUESTION:

How to use _only_ T-SQL find a missed numbers for particular week when
I'm insert a records?

Thanks.
ChapaiPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Rows are not records and you have no relational key in your
pseudo-code. Ignoring that the design is fundamentally bad because you
should be using temporal datatypes for temporal data, your table should
have looked like this:

CREATE TABLE Foobar
(week_nbr INTEGER NOT NULL
CHECK(week_nbr > 0),
day_nbr INTEGER NOT NULL
CHECK(day_nbr BETWEEN 1 AND 7),
PRIMARY KEY(week_nbr, day_nbr));

>> for every week we have a day_nbr from 1 to 7 or nothing (if no
record [sic] for that day), we can insert or delete day_nbr in any
order, at will . . How to use _only_ T-SQL to find a missed number for
particular week when I am inserting records [sic]? <<

This is a little ugly looking, but it is fast.

CREATE PROCEDURE InsertNewFoobar (@.new_week_nbr INTEGER)
BEGIN
DECLARE @.new_day_nbr INTEGER;
SET @.new_day_nbr
= CASE WHEN 1 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 1
WHEN 2 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 2
WHEN 3 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 3
WHEN 4 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 4
WHEN 5 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 5
WHEN 6 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 6
WHEN 7 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @.new_week_nbr)
THEN 7
ELSE NULL END;

INSERT INTO Foobar (week_nbr, day_nbr)
VALUES (@.new_week_nbr, @.new_day_nbr);
-- if you have 7 days already, then you get a primary key violation
-- you gave no specs on how to handle it

END:
In Standard SQL, the CASE expression could be in the VALUES () list|||Chapai (racecar@.mail.ru) writes:
> tool: MS SQL server 2000, T-SQL
> TABLE:
> [unique_id] [mynumber] [week]
> [unique_id] - bigint,primary key, identity auto-increnment
> [week] - int, 1-53, week number
> [mynumber] - int, 1 - 7, for every week, daily record one per day, up
> to 7 per week

I don't see the point with unique_id. Judging from your description
(week, mynumber) is unique. Then they should be the primary key.

> we can insert or delete mynubers in any order, at will
> EXAMPLE:
> week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value
>= 4
> week 2, mynumber 1,2,3,5,7 - so next mynumber = 4
> QUESTION:
> How to use _only_ T-SQL find a missed numbers for particular week when
> I'm insert a records?

Search Google or the subject "Thinking about code or SP", a recent
thread in microsoft.public.sqlserver.programming for a whole range of
suggestions to a similar problem.

Since this problem is constrained to 1-7, here is a more simple-minded
solution:

SELECT MIN (n)
FROM (SELECT n = 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7) n
WHERE NOT EXISTS (SELECT *
FROM weeks w
WHERE w.weekno = @.weekno
AND n.n = w.mynumber)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!

Thanks, I already find the same solution with while

create proc stupidproc ( @.week as int )
as

declare @.mynumber as int, @.availablenumber as int
set @.mynumber = 1

WHILE @.mynumber < 8
begin
IF NOT EXISTS ( select mynumber from MYTABLE

where mynumber = @.mynumber and [week]=...@.week)

begin
set @.availablenumber = @.mynumber
break
end
else
set @.mynumber = @.mynumber + 1

CONTINUE
end

select @.availablenumber|||Hi!

Erland Sommarskog wrote:

> I don't see the point with unique_id. Judging from your description
> (week, mynumber) is unique. Then they should be the primary key.

Just personal rule - always get a unique,independent id. I use sql in
web development - so to create a lists, etc.

> Search Google or the subject "Thinking about code or SP", a recent
> thread in microsoft.public.sqlserver.programming for a whole range of
> suggestions to a similar problem.
that was my second step. But it take a lot of wasted time.

> Since this problem is constrained to 1-7, here is a more
simple-minded
> solution:
> SELECT MIN (n)
> FROM (SELECT n = 1
> UNION ALL
> SELECT 2
> UNION ALL
> SELECT 3
> UNION ALL
> SELECT 4
> UNION ALL
> SELECT 5
> UNION ALL
> SELECT 6
> UNION ALL
> SELECT 7) n
> WHERE NOT EXISTS (SELECT *
> FROM weeks w
> WHERE w.weekno = @.weekno
> AND n.n = w.mynumber)
Cool. That is elegant. thanks.
Select x union all - that's nice.|||>> already find the same solution with while .. <<

Wrong. You have a proprietary, procedural answer that is
computationally equal to what I gave you. This is a BIG difference and
until you can see this, you will always be a 3GL programmer writing in
3GL programs in some proprietary, non-portable SQL dialect.

The whole point of non-procedural languages is that you tell it WHAT
you want and it figures oiut HOW to do it. Looping is a HOW and not a
WHAT.|||Hi!

--CELKO-- wrote:
> Wrong. You have a proprietary, procedural answer that is
> computationally equal to what I gave you.

yeah, right. I'm agree with you and I'm appreciate for your ideas and
help. But my version is more flexible and compact. what I gonna do with
your algorithm if I need more than 7 numbers? what about 50? 100?
Using a "while" I need to change only one variable and code are still
readable.
Easy to change, easy to support.
Portability is not important at all, especially for me, I'm a web
developer, so MS SQL cover all my (and my customers) needs. MS Access
cover the rest.

> until you can see this, you will always be a 3GL programmer writing
in
> 3GL programs in some proprietary, non-portable SQL dialect.
Ok. This is a real world. If you use ASP/VBscript/C#/.NET - Oracle
hosting are too expensive, Mysql useless.

> The whole point of non-procedural languages is that you tell it WHAT
> you want and it figures oiut HOW to do it. Looping is a HOW and not
a
> WHAT.
Nope. The whole point of non-procedural languages, and all other
programming languages - is to help you to make a money quickly. ;-)|||>> But my version is more flexible and compact. <<

No, your procedural coding is weak, too. Here is your algorithm in
SQL/PSM, which you can translate into dialect.

CREATE PROCEDURE StupidProc (IN my_week INTEGER)
LANGUAGE SQL
BEGIN
DECLARE answer_nbr INTEGER;
SET answer_nbr = 1;
WHILE answer_nbr < 8
DO IF NOT EXISTS
(SELECT *
FROM Foobar
WHERE day_nbr = answer_nbr
AND week_nbr = my_week)
THEN RETURN answer_nbr;
ELSE SET answer_nbr = answer_nbr + 1;
END IF;
END WHILE;
RETURN answer_nbr; -- 8 is an error
END;

The use of extra variables and the hidden GOTO's in BREAK and CONTINUE
would cost you points in any freshman programming class.

>> what I gonna do with your algorithm if I need more than 7 numbers?
what about 50? 100? <<

Use a Sequence table instead of a constructed table expression. Here
is a general version with pure Standard SQL

SELECT MIN (n)
FROM (SELECT seq FROM Sequence WHERE seq <= :n)
EXCEPT
(SELECT day_nbr
FROM Weeks AS
WHERE W.week_nbr = my_weeknbr) AS N(n);

>> The whole point of non-procedural languages, and all other
programming languages - is to help you to make money quickly. ;-) <<
And the only way you can do this is with bad programming??|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1105201718.985293.286980@.c13g2000cwb.googlegr oups.com...
>> >> The whole point of non-procedural languages, and all other
> programming languages - is to help you to make money quickly. ;-) <<
> And the only way you can do this is with bad programming??

Of course... that way the customer keeps coming back to you... until they
figure out they're paying too much. :-)|||Chapai (racecar@.mail.ru) writes:
> Just personal rule - always get a unique,independent id. I use sql in
> web development - so to create a lists, etc.

Not sure that I see the point, but as long as you have a UNIQUE constraint
on the real primary key, that's alright. Without the UNIQUE constraint,
you're putting the integrity of your database at stake.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!

--CELKO-- wrote:
> No, your procedural coding is weak, too. Here is your algorithm in
> SQL/PSM, which you can translate into dialect.

I have a better idea for you - just put your code into query analyzer
and try to execute it. Oops! It does not work? Why?
Open your eyes and read the group name. DB2? Super-duper-sql-theory?
Microsoft does not support this standard in sql server 2000.
Period.

> The use of extra variables and the hidden GOTO's in BREAK and
CONTINUE
> would cost you points in any freshman programming class.
Show me other faster and better way to use "while" in ms sql server
2000.
I'm wait.

> Use a Sequence table instead of a constructed table expression. Here
> is a general version with pure Standard SQL
aha. Hundreds sets like set @.i1 = 1, @.i2=2, tables, temporary tables,
cross calls, .. Sure. Sommarskog's sample was finer and more
interesting.

> And the only way you can do this is with bad programming??
It works? Works, fast? Fast. Simple? Simple. What else? Portability.
Strict adherence to standards. Ok. I'm not a student with ideas and not
an old professor with grey bolls - I'm MS web developer with hourly
rate.
Holy wars linux vs windows, c vs pascal. :-) You work on salary - right?|||Hi!

Erland Sommarskog wrote:

> Not sure that I see the point, but as long as you have a UNIQUE
constraint
> on the real primary key, that's alright. Without the UNIQUE
constraint,
> you're putting the integrity of your database at stake.

I'm a web developer. :-) Tomorrow customer can says to change
everything, or major part of logic. And I will get just couple hours to
realize that. other paradigm.|||Chapai (racecar@.mail.ru) writes:
>> Not sure that I see the point, but as long as you have a UNIQUE
>> constraint on the real primary key, that's alright. Without the UNIQUE
>> constraint, you're putting the integrity of your database at stake.
> I'm a web developer. :-) Tomorrow customer can says to change
> everything, or major part of logic. And I will get just couple hours to
> realize that. other paradigm.

If you work under these cirumstances, it's even more important to
have your constraints right. Stressed development, unevitably leads
to bugs creeping in. Constraint is a means of preventing at least some
of these bugs causing bad data to be persisted.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!

Erland Sommarskog wrote:

> If you work under these cirumstances, it's even more important to
> have your constraints right. Stressed development, unevitably leads

Ok. How you can link other tables without unique constraint?
Most fields are not unique, so, easy to get one guaranteed simple
unique field to reference. Or you know the other method?

table from discussed example (up to 7 workouts_num per week, for every
trainer/customer):

workout(workout_id, workout_num , workout_week, program_id, client_id)
and need to organise relations to table
program(program_id,program_name,trainer_id), table
exrcise(exercise_id,exercise_name),
table exercise_workout_link(workout_id,exercise_id)|||Chapai (racecar@.mail.ru) writes:
> Ok. How you can link other tables without unique constraint?

That's kind of difficult. Then again, I suggested that it was a
UNIQUE constraint that you should add to your table.

> Most fields are not unique, so, easy to get one guaranteed simple
> unique field to reference. Or you know the other method?
> table from discussed example (up to 7 workouts_num per week, for every
> trainer/customer):
> workout(workout_id, workout_num , workout_week, program_id, client_id)
> and need to organise relations to table
> program(program_id,program_name,trainer_id), table
> exrcise(exercise_id,exercise_name),
> table exercise_workout_link(workout_id,exercise_id)

It looks as if foreign-key constraint from workout to progam would be
possible. But not knowing the business rules, that is of course impossible
to tell.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

How to find which stored procs and UDFs reference a column

How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.

Thanks.

Which version of sql server are you using ?|||SQL Server 2000.|||You can try the sql server specific system table "sysdepends". Look it up in Books Online. I am not aware of an INFORMATION SCHEMA view that will help you in determining such references.|||

The sysdepends table seems helpful, but it only gives the table, not the columns. Here's the query I used:

SELECT sp.name as StoredProc, dep.name AS DependentObject
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id AND sp.xtype = 'P'
INNER JOIN sysobjects dep ON dep.id = sd.depid

Any further clues to linking the columns? (I suppose I could query for occurrences of the column name within the stored procedures depending on the column's table, but that's an approximation, since column names in the procedure text could be from a different table.)

|||

The depnumber column in sysdepends should give you the column id of the table that the procedure/function references. You can modify your query above to also join the depnumber column in sysdepends with the id column in syscolumns to get the name of the column.

Let me know if that works for you

|||

Seems I'm close, but the following query doesn't always yield complete results:

SELECT sp.name as StoredProc
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE tbl.name = 'MyTable' AND col.name = 'MyColumn'
ORDER BY sp.name

I'm trying to verify it as follows:

SELECT sp.name as StoredProc, tbl.name AS [Table], col.name AS [Column]
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE sp.name = 'MyProc'
ORDER BY tbl.name, col.name

Thank you for your consideration.

|||

If you are using dynamic sql to create the procedures/functions then sql server may not be able to track the references. Also, if you are using deferred name resolution [i.e create the procedure first and then create the table that is being referenced by the procedure] then sql server will not be able to track the references.

By any chance, is this the case?

|||

There are many cases under which the dependency tracking in SQL Server will not work. Here are the common cases:

1. Creation of dependent stored procedures in out-of-order fashion for example

2. Use of temporary tables or table variables in SELECT/DML statements will defer compilation of the statement so there will be no dependency information saved

3. Use of dynamic SQL

4. In case of permanent tables, if the object doesn't exist then deferred name resolution / compilation will kick-in at run-time and there will be no dependency information for this case also

Please take a look at the blog entry below for some queries on how to do this in SQL Server 2005 assuming that the dependency information is present.

http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx

So given the various restrictions it is probably unlikely that you have dependency information for most objects other than references, schema bound objects etc. You will have to mostly maintain this information manually or use your source code control system to scan your scripts assuming you use some keywords mechanism to tag scripts for example.

|||

I take it back -- after carefully comparing results, it looks like it's working great. Thanks so much for all your help!!

SELECT sp.name AS StoredProc
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE tbl.name = 'MyTable' AND col.name = 'MyColumn'
ORDER BY sp.name

Confirming/cross-checking query:

SELECT obj.[name], cmt.[text]
FROM syscomments cmt
INNER JOIN sysobjects obj ON obj.id = cmt.id
WHERE text like '%MyTable%'
AND [text] LIKE '%MyColumn%'

Reverse query:

SELECT sp.name as StoredProc, tbl.name AS [Table], col.name AS [Column]
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE sp.name = 'MyProc'
ORDER BY tbl.name, col.name

|||I've discovered that the sysdepends table is not reliable/complete unless the stored procedure is created (or recreated) after the table(s) that it references. [Or at least, I can say that querying for columns referenced by a proc may not show up any, but then if the proc is dropped and recreated, the same query can now show its column dependencies.]|||

As Umachandar pointed out, there are instances where sysdepends will not work correctly.

|||Recreating SPs or altering SPs are costly operations since they will block access to the SP metadata. So you have to probably schedule this during a maintenance window. And if you don't have a mechanism to know which ones to alter then you will have to do this for all the SPs and this can take considerable time depending on the number of SPs/UDFs in the database. So there are several issues if you rely completely on the server dependency information.

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
--
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.giganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
Karen
Quickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegro ups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.giganews.com ...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael
|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.
|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegro ups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.gi
ganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.gi
ganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what tables/views/functions a stored proc uses

Hi All,
Is there any way How to find what tables/views/functions a stored proc
uses.
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***Try sp_depends
Thomas
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:%23VriiaVXFHA.1468@.tk2msftngp13.phx.gbl...
> Hi All,
> Is there any way How to find what tables/views/functions a stored proc
> uses.
> Thanks for your help.
> *** Sent via Developersdex http://www.examnotes.net ***|||Hai
Object that dependent on <<table_name>>
exec sp_MSdependencies N'[dbo].[<<Table_name>>]', null, 1315327
Object that <<table_name>> depends on
exec sp_MSdependencies N'[dbo].[<<Table_name>>]', null, 1053183
Thanks
NR. Harisutarsan
*** Sent via Developersdex http://www.examnotes.net ***|||Thanks. That works very well.
*** Sent via Developersdex http://www.examnotes.net ***sql

How to find unused Objects?

Is there a way in SQL I can tell when the last time a stored procedure was run or a table was accessed? I know there are a lot of objects in my system that are probably no longer being used but what is the best way to go about identifing these objects?

Gk

That is not going to be a 'simple' process. There is nothing inherrent in SQL Server that will give you that information with certainty.

You could set up a Profiler trace to capture the procedure/function/view/table name over time.

There are some Third party products that will follow the dependency trees, and also track usage. Check Tibor's list.

The process I follow is this:

When I identify an object that is a removal candidate:

All Stored Procedures/Functions/views/Tables that have been created in a database for solely administrative purposes are, in SQL 2000 named [dbs_, dbf_, dbv_, db_] and in SQL 2005, added to the [Admin] schema. I have a 'home-grown' .NET tool that will cycle through the source control store, examining application code, compiling a list of command.text statements, Since my practice is to require the use of stored procedures for all data access, the command.text is a list of Stored Procedures. I then check object dependencies against the list. I now have my 'first pass' candidate list. I first rename the candidate object (add 'x' to the beginning and they all sort to the bottom of the list). If something then 'breaks', it is very quick to change the name back and restore functionality. I leave the renamed objects for 3-6 months before final deletion. (Some things may be rarely used, but they may be for an 'important' process -such as reporting.) If it is obvious that the object is for a reporting process, I may leave it for a year -there are annual reports... When an object is finally removed, I script it out and store the script in an object archive. (Who knows, perhaps that object had some 'nice' code in it that I can refer to later.)sql

How to find the table used in Stored Procedure By Query

Hi,

I need to a find table which is used by list of stored procedures.

Can you please send me the query which is used?

Thanks and Regards

Abdul M.G

There is no built-in function in SQL Server that can do this. You need to search sysobjects table for matching strings.

Here's a piece of code I found that will list all stored procedures that reference a certain table.

SELECT DISTINCT so.name FROMsyscomments scINNERJOINsysobjects soon sc.id=so.idWHERE sc.textLIKE'%tablename%'

[Original source here]

|||

A much simpler way is to get the output of the sp_depends system stored procedure. This will give you any tables, views, stored procedures, user-defined functions or triggers used by the given stored procedure. Usage is sp_depends 'YourSPName'. Required tables will have a value of 'user table' in the type field in the output.

How to find the SQL start up acct from SQL?

Is there a system stored procedure to show the SQL Server Startup Service
Account id?
Thanks
Alex
No. You can read it from the registry though using
xp_instance_regread. If you run profiler while checking the
value in Enterprise Manager, you can see what is used to
view the account in EM - it's along the lines of:
DECLARE @.serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@.ServiceAccount OUTPUT,
N'no_output'
SELECT @.Serviceaccount
-Sue
On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
Au@.discussions.microsoft.com> wrote:

>Is there a system stored procedure to show the SQL Server Startup Service
>Account id?
>Thanks
>Alex
|||Thank you very much. I did venture into the registry but did not know the
xp_regread stored procedure to read registry from within SQL Server.
I am developing a sp to kill all active users apart from the processes run
by Service account, hence the question.
Thanks again
"Sue Hoegemeier" wrote:

> No. You can read it from the registry though using
> xp_instance_regread. If you run profiler while checking the
> value in Enterprise Manager, you can see what is used to
> view the account in EM - it's along the lines of:
> DECLARE @.serviceaccount varchar(100)
> EXECUTE master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',
> N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
> N'ObjectName',
> @.ServiceAccount OUTPUT,
> N'no_output'
> SELECT @.Serviceaccount
> -Sue
> On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
> Au@.discussions.microsoft.com> wrote:
>
>
|||you might also like to try this...
if object_id('tempdb..#Results')is not null drop table #Results
create table #Results(value nvarchar(4000))
insert into #Results(value)
exec xp_cmdshell 'wmic service get name,startname'
select
*
from
#Results
where
value like 'MSSQLSERVER %'
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Alex Au" wrote:

> Is there a system stored procedure to show the SQL Server Startup Service
> Account id?
> Thanks
> Alex

How to find the SQL start up acct from SQL?

Is there a system stored procedure to show the SQL Server Startup Service
Account id?
Thanks
AlexNo. You can read it from the registry though using
xp_instance_regread. If you run profiler while checking the
value in Enterprise Manager, you can see what is used to
view the account in EM - it's along the lines of:
DECLARE @.serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQ
LSERVER',
N'ObjectName',
@.ServiceAccount OUTPUT,
N'no_output'
SELECT @.Serviceaccount
-Sue
On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
Au@.discussions.microsoft.com> wrote:

>Is there a system stored procedure to show the SQL Server Startup Service
>Account id?
>Thanks
>Alex|||Thank you very much. I did venture into the registry but did not know the
xp_regread stored procedure to read registry from within SQL Server.
I am developing a sp to kill all active users apart from the processes run
by Service account, hence the question.
Thanks again
"Sue Hoegemeier" wrote:

> No. You can read it from the registry though using
> xp_instance_regread. If you run profiler while checking the
> value in Enterprise Manager, you can see what is used to
> view the account in EM - it's along the lines of:
> DECLARE @.serviceaccount varchar(100)
> EXECUTE master.dbo.xp_instance_regread
> N'HKEY_LOCAL_MACHINE',
> N'SYSTEM\CurrentControlSet\Services\MSSQ
LSERVER',
> N'ObjectName',
> @.ServiceAccount OUTPUT,
> N'no_output'
> SELECT @.Serviceaccount
> -Sue
> On Tue, 3 May 2005 04:44:04 -0700, "Alex Au" <Alex
> Au@.discussions.microsoft.com> wrote:
>
>|||you might also like to try this...
if object_id('tempdb..#Results')is not null drop table #Results
create table #Results(value nvarchar(4000))
insert into #Results(value)
exec xp_cmdshell 'wmic service get name,startname'
select
*
from
#Results
where
value like 'MSSQLSERVER %'
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Alex Au" wrote:

> Is there a system stored procedure to show the SQL Server Startup Service
> Account id?
> Thanks
> Alex