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 trailing spaces in a column

Is there a way to find out (Script, tool e.t.c) if there
are any trailing spaces in a varchar column '
Please correct me if I am wrong but if you specify a
varchar column with 255 and enter only 15 character, it
should occupy only 15 Right '
It seems like we have varchar columns in several tables
with trailing spaces. I am thinking it is an application
problem but could it be SQL Server problem '
Thanks for any help.........> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
One method:
SELECT MyColumn from MyTable
WHERE DATALENGTH(MyColumn) <> DATALENGTH(RTRIM(MyColumn))
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct (plus overhead).
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
Probably an application issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........|||Steve,
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct. However, the application might include trailing spaces in the INSERT statement. Whether SQL Server
will keep those of not depends on the setting of ANSI_PADDINGS when the table (or column) was created. Use
"sp_help tblname" to find out.
To find rows with trailing spaces, you can do something like (be prepared for a table scan):
SELECT * FROM authors WHERE SUBSTRING(REVERSE(au_lname), 1, 1) = ' '
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <anonymous@.discussions.microsoft.com> wrote in message news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........|||Thanks for all your help Dan & Tibor.....
>--Original Message--
>Is there a way to find out (Script, tool e.t.c) if there
>are any trailing spaces in a varchar column '
>Please correct me if I am wrong but if you specify a
>varchar column with 255 and enter only 15 character, it
>should occupy only 15 Right '
>It seems like we have varchar columns in several tables
>with trailing spaces. I am thinking it is an application
>problem but could it be SQL Server problem '
>Thanks for any help.........
>.
>sql

How to find trailing spaces in a column

Is there a way to find out (Script, tool e.t.c) if there
are any trailing spaces in a varchar column ?
Please correct me if I am wrong but if you specify a
varchar column with 255 and enter only 15 character, it
should occupy only 15 Right ?
It seems like we have varchar columns in several tables
with trailing spaces. I am thinking it is an application
problem but could it be SQL Server problem ?
Thanks for any help.........
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column ?
One method:
SELECT MyColumn from MyTable
WHERE DATALENGTH(MyColumn) <> DATALENGTH(RTRIM(MyColumn))

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
Correct (plus overhead).

> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem ?
Probably an application issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column ?
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem ?
> Thanks for any help.........
|||Steve,

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
Correct. However, the application might include trailing spaces in the INSERT statement. Whether SQL Server
will keep those of not depends on the setting of ANSI_PADDINGS when the table (or column) was created. Use
"sp_help tblname" to find out.
To find rows with trailing spaces, you can do something like (be prepared for a table scan):
SELECT * FROM authors WHERE SUBSTRING(REVERSE(au_lname), 1, 1) = ' '
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <anonymous@.discussions.microsoft.com> wrote in message news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column ?
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem ?
> Thanks for any help.........

How to find trailing spaces in a column

Is there a way to find out (Script, tool e.t.c) if there
are any trailing spaces in a varchar column '
Please correct me if I am wrong but if you specify a
varchar column with 255 and enter only 15 character, it
should occupy only 15 Right '
It seems like we have varchar columns in several tables
with trailing spaces. I am thinking it is an application
problem but could it be SQL Server problem '
Thanks for any help.........> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
One method:
SELECT MyColumn from MyTable
WHERE DATALENGTH(MyColumn) <> DATALENGTH(RTRIM(MyColumn))

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct (plus overhead).

> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
Probably an application issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1a84e01c44eec$32d0a740$a301280a@.phx
.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........|||Steve,

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct. However, the application might include trailing spaces in the INSER
T statement. Whether SQL Server
will keep those of not depends on the setting of ANSI_PADDINGS when the tabl
e (or column) was created. Use
"sp_help tblname" to find out.
To find rows with trailing spaces, you can do something like (be prepared fo
r a table scan):
SELECT * FROM authors WHERE SUBSTRING(REVERSE(au_lname), 1, 1) = ' '
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <anonymous@.discussions.microsoft.com> wrote in message news:1a84e01c44eec$32d0a740$a
301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........

How to find the Froiengn key refrence column and refrence table through T-SQL

Hi,
I am creating a tool for generating SQL Scripts.
I want the sql statement for getting the table name , column name ,
reference table name ,reference column name of a particular foreign key.
In this case I know only the foreign key name.
Thanks and Regards,
SathiamoorthyOJ has written this script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Sathiamoorthy" <someone@.microsoft.com> wrote in message
news:OZP0tPJLGHA.536@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am creating a tool for generating SQL Scripts.
> I want the sql statement for getting the table name , column name ,
> reference table name ,reference column name of a particular foreign key.
> In this case I know only the foreign key name.
> Thanks and Regards,
> Sathiamoorthy
>|||Thank you very much.
sathyamoorthy
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uGo7LUJLGHA.1180@.TK2MSFTNGP09.phx.gbl...
> OJ has written this script
> create procedure usp_findreferences
> @.tbname sysname=null
> as
> set nocount on
>
> Print 'Referenced:'
> select c1.table_name,
> c1.column_name,
> fkey=r.constraint_name,
> referenced_parent_table=c2.table_name,
> c2.column_name
> from information_schema.constraint_column_usage c1 join
> information_schema.referential_constraints r on
> c1.constraint_name=r.constraint_name
> join information_schema.constraint_column_usage c2 on
> r.unique_constraint_name=c2.constraint_name
> where c1.table_name=coalesce(@.tbname,c1.table_name)
> order by case when @.tbname is null then c1.table_name else c2.table_name
end
>
> print ''
> print 'Referencing:'
> select c1.table_name,
> c1.column_name,
> fkey=r.constraint_name,
> referencing_child_table=c2.table_name,
> c2.column_name
> from information_schema.constraint_column_usage c1 join
> information_schema.referential_constraints r on
> c1.constraint_name=r.unique_constraint_name
> join information_schema.constraint_column_usage c2 on
> r.constraint_name=c2.constraint_name
> where c1.table_name=coalesce(@.tbname,c1.table_name)
> order by case when @.tbname is null then c1.table_name else c2.table_name
end
> go
>
> --test run
> exec usp_findreferences 'Orders'
> drop proc usp_findreferences
> "Sathiamoorthy" <someone@.microsoft.com> wrote in message
> news:OZP0tPJLGHA.536@.TK2MSFTNGP09.phx.gbl...
>