Determine used fields/tables in database

Greetings to all!
I was curious if there was a way to determine what fields are in use by
stored procedures and/or views in a database. If there's a way through
SQL or through VB .NET, I would be most appreciative to learn about it.
Thanks,
Kyjan
"Kyjan" <HolySaphAngel@.gmail.com> wrote in message
news:1141065761.449583.226070@.u72g2000cwu.googlegr oups.com...
> Greetings to all!
> I was curious if there was a way to determine what fields are in use by
> stored procedures and/or views in a database. If there's a way through
> SQL or through VB .NET, I would be most appreciative to learn about it.
> Thanks,
> Kyjan
>
There may be an easier way, but you might look at how the sp_depends sproc
does it.
Rick Sawtell
MCT, MCSD, MCDBA
|||This way does not look at views. Is there a way to include those?
Kyjan
sql

Determine used fields/tables in database

Greetings to all!
I was curious if there was a way to determine what fields are in use by
stored procedures and/or views in a database. If there's a way through
SQL or through VB .NET, I would be most appreciative to learn about it.
Thanks,
Kyjan"Kyjan" <HolySaphAngel@.gmail.com> wrote in message
news:1141065761.449583.226070@.u72g2000cwu.googlegroups.com...
> Greetings to all!
> I was curious if there was a way to determine what fields are in use by
> stored procedures and/or views in a database. If there's a way through
> SQL or through VB .NET, I would be most appreciative to learn about it.
> Thanks,
> Kyjan
>
There may be an easier way, but you might look at how the sp_depends sproc
does it.
Rick Sawtell
MCT, MCSD, MCDBA|||This way does not look at views. Is there a way to include those?
Kyjan

Determine Duration between Two Dates

All
I have a table of members and I would like to determine how long each member
has been a member based upon the current date. I would like the result
retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
assistance would be appreciated.
CREATE TABLE member
(
MemberID INT,
DateJoined SMALLDATETIME
)
INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
Desired Result:
1 1999-01-11 2005-10-13 6y 9m 2d
2 2004-12-26 2005-10-13 0y 10m 18d
3 2005-01-01 2005-10-13 0y 10m 12d
ThanksHi David,
There must be a simpler way than this, but what the heck...
SELECT id, dt, y, m,
diff_d - CASE WHEN dt_ym + diff_d > today THEN 1 ELSE 0 END AS d
FROM
(
SELECT *, DATEDIFF(day, dt_ym, today) AS diff_d
FROM
(
SELECT id, dt, today, y, m,
DATEADD(month, m, dt_y) AS dt_ym
FROM
(
SELECT id, dt, today, y, dt_y,
m_diff - CASE WHEN DATEADD(month, m_diff, dt_y) > today
THEN 1 ELSE 0 END AS m
FROM
(
SELECT *, DATEDIFF(month, dt_y, today) AS m_diff
FROM
(
SELECT *, DATEADD(year, y, dt) AS dt_y
FROM
(
SELECT id, dt, today,
y_diff - CASE WHEN DATEADD(year, y_diff, dt) > today
THEN 1 ELSE 0 END AS y
FROM
(
SELECT *, DATEDIFF(year, dt, today) AS y_diff
FROM
(
SELECT MemberID AS id, DateJoined AS dt,
CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS DATETIME) AS today
FROM member
) AS D1
) AS D2
) AS D3
) AS D4
) AS D5
) AS D6
) AS D7
) AS D8;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"David" <David@.discussions.microsoft.com> wrote in message
news:44CF768B-1213-453F-8A62-024857AF5089@.microsoft.com...
> All
> I have a table of members and I would like to determine how long each
> member
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks|||Hi David
Probably you can check the link.
http://chanduas.blogspot.com/2005/0...lating-age.html
This is not the exact solution but can give u an idea
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David" wrote:

> All
> I have a table of members and I would like to determine how long each memb
er
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks

Determine All User Databases

Hi All
I have a SQL Server 2005 server and I was wanting to determine all of the
user databases and I was wondering if there was a better way to do this
instead of the following script:
select name
from master.sys.databases
where name not in ('mater', 'tempdb', 'msdb', 'model')
Thanks
If these are the only 4 db's that you want to eliminate, then you could also
query based on database_id, and then it wouldn't matter if you don't spell
one of the database names correctly. :-) You also don't need a 3-part name
when you use the new metadata views.
SELECT name FROM sys.databases
WHERE database_id > 4
If you set up replication, some servers will get a distribution database,
and you may want to eliminate that one also.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7B789F7B-702A-4109-9FEB-D8E6F27A4FE5@.microsoft.com...
> Hi All
> I have a SQL Server 2005 server and I was wanting to determine all of the
> user databases and I was wondering if there was a better way to do this
> instead of the following script:
> select name
> from master.sys.databases
> where name not in ('mater', 'tempdb', 'msdb', 'model')
> Thanks

Determine All User Databases

Hi All
I have a SQL Server 2005 server and I was wanting to determine all of the
user databases and I was wondering if there was a better way to do this
instead of the following script:
select name
from master.sys.databases
where name not in ('mater', 'tempdb', 'msdb', 'model')
ThanksIf these are the only 4 db's that you want to eliminate, then you could also
query based on database_id, and then it wouldn't matter if you don't spell
one of the database names correctly. :-) You also don't need a 3-part name
when you use the new metadata views.
SELECT name FROM sys.databases
WHERE database_id > 4
If you set up replication, some servers will get a distribution database,
and you may want to eliminate that one also.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:7B789F7B-702A-4109-9FEB-D8E6F27A4FE5@.microsoft.com...
> Hi All
> I have a SQL Server 2005 server and I was wanting to determine all of the
> user databases and I was wondering if there was a better way to do this
> instead of the following script:
> select name
> from master.sys.databases
> where name not in ('mater', 'tempdb', 'msdb', 'model')
> Thanks