Sunday, February 26, 2012

Find databases in FULL recovery mode

I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.com
Why don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:

> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>

Find databases in FULL recovery mode

I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:
> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>

Find databases in FULL recovery mode

I am maintaining the backend SQL 2000 Server for a number of Share
Point 2003 application servers. The problem I am having is that new
databases are created by the Administrators of the SharePoint app
which is not a problem, but nightly I run the Integrity and
Optimization jobs on all user sql databases and this fails because the
new databases are either in FULL recovery mode. I just want to keep
the recovery modes for all user db's in SIMPLE recovery.
Is there a script that would notify me of a new database created and
what recovery mode it is in? or maybe a nightly job that could find
all databases in Full mode except for the system dbs and then change
the user db from FULL to SIMPLE before my optimization job runs?
I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
is creating a databases it modifies it to FULL.
Thanks for any help,
Richard Rios
richardrios@.earthnewsdaily.comWhy don't you use
SELECT DATABASEPROPERTYEX('model' , 'recovery' )
Write an sp which picks database names from master..sysdatabase
Thanks
GYK
"Richard Rios" wrote:

> I am maintaining the backend SQL 2000 Server for a number of Share
> Point 2003 application servers. The problem I am having is that new
> databases are created by the Administrators of the SharePoint app
> which is not a problem, but nightly I run the Integrity and
> Optimization jobs on all user sql databases and this fails because the
> new databases are either in FULL recovery mode. I just want to keep
> the recovery modes for all user db's in SIMPLE recovery.
> Is there a script that would notify me of a new database created and
> what recovery mode it is in? or maybe a nightly job that could find
> all databases in Full mode except for the system dbs and then change
> the user db from FULL to SIMPLE before my optimization job runs?
> I have set the MODEL database to SIMPLE mode, but when SharePoint 2003
> is creating a databases it modifies it to FULL.
> Thanks for any help,
> Richard Rios
> richardrios@.earthnewsdaily.com
>

Find Database Users

I'm wondering if there is a way I can query against a
system table to find out all the users on a server and
what permissions they all have? If anyone has any ideas I
would really appreciate it! Thanks!Ann
Aaron Bertrand wrote
This will only cover those who have
been explicitly given access to the procs using GRANT.
DECLARE @.username VARCHAR(32)
SELECT @.username = '?'
SELECT o.name, CanExec = CASE WHEN p.id = o.id THEN 'Yes' ELSE 'No' END
FROM sysobjects o LEFT OUTER JOIN syspermissions p
ON o.id = p.id
AND p.grantee = USER_ID(@.username)
"Ann" <ao_949@.hotmail.com> wrote in message
news:127901c4f519$8e655690$a401280a@.phx.gbl...
> I'm wondering if there is a way I can query against a
> system table to find out all the users on a server and
> what permissions they all have? If anyone has any ideas I
> would really appreciate it! Thanks!
>

Find database access by tcp connection

Ok, lets see if I can put what I am looking for into words.
I run a MSSQL server, I show in my logs that a connection is being made to my server from a remote ip. I have contact the owner of the ip in question and they state that they have no buisness with us and they should not be connected to us. According to netstat the connection to us is established on 2 different remote ports to 1433. My question, is there any logging on sql that I can turn on, look at to see what database this connection is accessing? ThanksHow about the windows event viewer or sql profiler..

However...how are they connecting id they do not have a login?|||Originally posted by Brett Kaiser
How about the windows event viewer or sql profiler..

However...how are they connecting id they do not have a login?

They must have a userid and password to connect yes, I am trying to find out who it is. None of our clients that I know of are connecting from this location. I'm not familiar with the sql profiler. Where might this be.

Thanks|||It comes with the sql server client tools...

And this guy gave a presentation on it...

http://weblogs.sqlteam.com/billg/archive/2003/11/08/460.aspx|||Hmm. You are really plugging those weblogs these days, Brett ;-). <opening>But I bet I can't find a decent recipe for a margarita there.</opening>

What logs are showing the connections? Do you have success auditing for security turned on on the server?|||Originally posted by MCrowley
Hmm. You are really plugging those weblogs these days, Brett ;-). <opening>But I bet I can't find a decent recipe for a margarita there.</opening>

What logs are showing the connections? Do you have success auditing for security turned on on the server?

Is this a setup?

http://weblogs.sqlteam.com/brettk/archive/2003/10/02/223.aspx

find database

i want a large database for datamining. but i don't find. can you help me?.

You could try the Datasets for Data Mining and Knowledge Discovery.

Good luck,

|||you can help me find database of shop.It contents products,sale,Customers and products are bought by Customer.

Find Current Date Time Span SQL

Any body have an Idea on this... Last problem of my Calendar Application.

To keep my calendar loading fast and efficent I have a SP that loads data to a datatable to read from c#.

My sp reads this

(
@.ID int,
@.DateStart datetime,
@.DateEnd datetime
)
AS

SELECT JobDateStart, JobDateEnd, EstimateDate, cLastName,
cCity, ContractPrice, JobDescription, Status
FROM Customers
WHERE (ID = @.ID AND JobDateStart Between @.DateStart AND @.DateEnd + 1
OR JobDateEnd Between @.DateStart AND @.DateEnd + 1)

Now My problem is that the DateStart and DateEnd values are used to pull information from a DB. So lets say that I have a job that starts on November 30th and ends on January 3rd.
My calendar will display the record on the 30th day of Nov and on the 1st through 3rd fo Jan, however when I look at December there is NO DATA there. which is the expected result because the dates do not fall within the start and ends dates.

My question is how query the database to pull back my month spanding record without pulling back all the data in the database for performace?

My thought is something like the date span falls into the range between the start and end, but I cannot find anything????? Please help, I know it is probaly simple right??Found the answer, just took THINKING LESS rather than more..

add and OR statement like this, it will get the 1 day events and current month event and the spanning events.

SELECT JobDateStart, JobDateEnd, EstimateDate, cLastName,
cCity, ContractPrice, JobDescription, Status
FROM Customers
WHERE (ID = @.ID AND
/*FIX SPANNING MONTHS / YEAR JOBS*/
@.DateStart BETWEEN JobDateStart AND JobDateEnd +1
/*SELECT 1 DAY OR 1 MONTH JOBS*/
OR JobDateStart Between @.DateStart AND @.DateEnd + 1)

It really was that easy. Less load on the db so I can loop through the DataTable and do my sorting with no effect on the DB.

Find Countries.. MDX

I want to get all the country names from a cube where another Key figure is NOT equal spaces or zeros. Please let me know if some one can help me.

My Cube is CFS_PERF/CFS_PERF_CFSPERF_GL
Want to get 0PAYER_COUNTRY where

[Measures].[3M1DTA8FO1HVYVOI6HUABEXHX] is NOT equal to zero

Please help !!!

Try this:

SELECT [Measures].[3M1DTA8FO1HVYVOI6HUABEXHX] ON 0,

NON EMPTY [OPAYER_COUNTRY].members ON 1

FROM CFS_PERF_CFSPERF_GL

Find Counties in area

We have the need to determine surrounding counties of a particular county.

For example how can I determine, if I entered a zip code for Contra Costa and the property is also right on the line of alameda that Alameda is a neighboring county of Contra Costa

Could it be determined by the longitude and latitude of the zip code in comparison with the counties?

Hello,

Although I'm sure a geospacial co-ordinate system could be used to determine this, most people think it overkill and just use a proximity table :)

tCounty (CountyID int, CountyName varchar)

tCountyAdjoined(CountyID int FK to tCounty.CountyID, AdjoiningCountyID int FK to tCounty.CountyID)

So if Contra Costa was near Alameda, tCountry might contain rows:

1, Contra Costa

2, Alameda

And tCountyAdjoined would contain:

1, 2

Obviously, a simple join then returns all counties that adjoin a particular County.

Unfortunatly, you'll need to manually determine and input the proximity values (unless your postal service supplies these details as in Australia.)

Cheers,

Rob

Find Consecutive Occurrences

Hi,
I am in need of a query which would find the same customer coming in for three or more consecutive dates. To elaborate

I have a details table where I capture the following details

CustID, DateofPurchase, PurchaseDetails

I need a query to find how many customers have come in everyday consecutive day and count of the same for the a given period, say a month. Can anyone help me with a query for the same.

Thanks for your help in advance.
Regards
DineshProbably, Please read the hint link in the sticky at the top of the forum to help us with more information|||Brett,

In my table I have details as below

CustID DateofPurchase PurchaseDetails
------------------
1 01/05/2006 Item1, Item2, Item3
2 01/06/2006 Item2, Item5, Item1, Item7
1 01/06/2006 Item4, Item5, Item6
3 01/06/2006 Item4, Item5, Item6
1 01/07/2006 Item3, Item4, Item5
3 01/07/2006 Item1, Item2, Item3
1 01/08/2006 Item1, Item2, Item3
2 01/08/2006 Item4, Item5, Item6
3 01/08/2006 Item4, Item5, Item6
2 01/09/2006 Item2, Item5, Item1, Item7
2 01/10/2006 Item1, Item2, Item3, Item4
1 01/10/2006 Item1, Item2, Item3, Item4
1 01/11/2006 Item4, Item5, Item6

I need the query which will return say Cust ID 1 has come 4 days in a row, or Cust ID 2 has come 3 days in a Row.
I want the below output

Cust ID No of Consecutive Days
1 4
2 3
3 3
2 2
1 2

Would this help. Please let me know.

find computed columns in all tables

How do i find all the computed columns in all tables and also what the
computation is ? ThanksHassan
Have a look at COLUMNPROPERTY ( id , column , property ) in the BOL.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OaphViKRFHA.648@.TK2MSFTNGP14.phx.gbl...
> How do i find all the computed columns in all tables and also what the
> computation is ? Thanks
>|||Thats a start, but how do I find the formula for that computed column
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eLMc%23qKRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> Hassan
> Have a look at COLUMNPROPERTY ( id , column , property ) in the BOL.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OaphViKRFHA.648@.TK2MSFTNGP14.phx.gbl...
>|||You find the source code in syscomments:
use tempdb
go
CREATE TABLE t(c1 int, c2 AS c1 * 23)
GO
SELECT text FROM syscomments
WHERE id = object_id('t')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:%23iIHAuKRFHA.3560@.TK2MSFTNGP14.phx.
gbl...
> Thats a start, but how do I find the formula for that computed column
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eLMc%23qKRFHA.3076@.TK2MSFTNGP14.phx.gbl...
>|||You can find the formula for a computed column in the syscomments table
(undocumented for this purpose). The undocumented procedure
sp_MShelpcolumns also provides this information, but you may find it
easier to use something like this:
SELECT o.name as TableName, c.name as ColumnName, cm.text as Formula
FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
INNER JOIN syscomments cm ON cm.id = c.id and cm.number = c.colid
WHERE o.type='U'
Razvan|||SELECT OBJECT_NAME(col.id) AS TableName, col.name AS ColumnName, com.text AS
Formula
FROM syscolumns col
INNER JOIN syscomments com
ON col.id = com.id
AND col.colid = com.number
WHERE OBJECTPROPERTY(col.id, 'IsUserTable') = 1
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23iIHAuKRFHA.3560@.TK2MSFTNGP14.phx.gbl...
> Thats a start, but how do I find the formula for that computed column
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eLMc%23qKRFHA.3076@.TK2MSFTNGP14.phx.gbl...
>

Find columns which execute updatetriggers

Hello,

I want to find the columns which execute updatetriggers. Is there in sqlserver a way to find out?

In Oracle you can say:

SELECT COLUMN_NAME
FROM DBA_TRIGGER_COLS
WHERE TRIGGER_OWNER = 'MyOwner'
AND TRIGGER_NAME = 'JobUpdate'
AND TABLE_NAME = 'Job'
AND COLUMN_LIST = 'YES'

I tried: EXEC sp_depends "dba.JobUpdate".

This says nothing about the updatecolumns.

Can anybody help?

thanx and greetz

C Dunnink
The NetherlandsColumns which execute update triggers?

Triggers are table-specific. Not column-specific.

If you want to find tables that have triggers on them you can search the schema or the system tables.|||When you create a update trigger you can specify at the update of which column the trigger should go off. For example:

CREATE TRIGGER DBA.TestTrigger ON DBA.Test
FOR UPDATE AS
IF UPDATE(TestCol1) OR UPDATE(TestCol2)
BEGIN
NULL;
END;

Now I want to call a system-sp or do a select to get TestCol1 and TestCol2.

Is it clear now?|||Since that is logical code within the trigger, I doubt that it is available in the system tables or schema, other than doing a text search for the name of the column or the key string "UPDATE(".

Find column names used in TSQL and Views

Is there are way to find out which View and Stored Procs contain a column
name or even a string? I am trying to rename a columnin a table from
Cust_name to CustName and want to find what views/stored procs will crash.
Thanks in advance,
Stewart Rogers
DataSort Software, L.C.
How do I find a stored procedure containing <text>?
http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
AMB
"Datasort" wrote:

> Is there are way to find out which View and Stored Procs contain a column
> name or even a string? I am trying to rename a columnin a table from
> Cust_name to CustName and want to find what views/stored procs will crash.
> Thanks in advance,
> --
> Stewart Rogers
> DataSort Software, L.C.
|||looks like AMesa has you covered for sprocs.
This query will get you the views:
use pubs
go
select v.name
from sys.columns c (nolock) inner join sys.views v (nolock) on v.object_id =
c.object_id
where c.name = 'title'
TheSQLGuru
President
Indicium Resources, Inc.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:A3083FBC-6C03-4A28-8B48-F21D8297D3C4@.microsoft.com...[vbcol=seagreen]
> How do I find a stored procedure containing <text>?
> http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
>
> AMB
>
> "Datasort" wrote:

Find column names used in TSQL and Views

Is there are way to find out which View and Stored Procs contain a column
name or even a string? I am trying to rename a columnin a table from
Cust_name to CustName and want to find what views/stored procs will crash.
Thanks in advance,
--
Stewart Rogers
DataSort Software, L.C.How do I find a stored procedure containing <text>?
http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
AMB
"Datasort" wrote:
> Is there are way to find out which View and Stored Procs contain a column
> name or even a string? I am trying to rename a columnin a table from
> Cust_name to CustName and want to find what views/stored procs will crash.
> Thanks in advance,
> --
> Stewart Rogers
> DataSort Software, L.C.|||looks like AMesa has you covered for sprocs.
This query will get you the views:
use pubs
go
select v.name
from sys.columns c (nolock) inner join sys.views v (nolock) on v.object_id =c.object_id
where c.name = 'title'
TheSQLGuru
President
Indicium Resources, Inc.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:A3083FBC-6C03-4A28-8B48-F21D8297D3C4@.microsoft.com...
> How do I find a stored procedure containing <text>?
> http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html
>
> AMB
>
> "Datasort" wrote:
>> Is there are way to find out which View and Stored Procs contain a column
>> name or even a string? I am trying to rename a columnin a table from
>> Cust_name to CustName and want to find what views/stored procs will
>> crash.
>> Thanks in advance,
>> --
>> Stewart Rogers
>> DataSort Software, L.C.

Find column name and datatype of a given table

Hi,
How do we find the "column name" and "data type" of all the columns in a table. Assuming that I know the Table name or Table Object ID. I am using Microsoft SQL Server 2000.
Thanks
-SudhakarHi sudhakar_112

Have a peak at the INFORMATION_SCHEMA tables in BoL - terribly useful.

Specifically:
SELECT COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

HTH|||Thank you..

using the following query lists only the columns that are acessible to the user. If the user does not have permission on a column it will not be displayed. But I want a query to display all column names irrespective of the user has any permissions to modify or select.

Thanks
-Sudhakar

Hi sudhakar_112

Have a peak at the INFORMATION_SCHEMA tables in BoL - terribly useful.

Specifically:
SELECT COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

HTH|||You can construct a query on the syscolumns and sysobjects tables - these are not dependent on the user permissions.

find changed columns in a new version of a row

Hi,
I wanted to know if theres a fast way of knowing which columns in a row have
changed.
I have 2 versions of rows-old and new.
My table is as follows
CPK compositePrimaryKey--combination of 3 keys
versionID uniquidentifier
param1 int
param1 char(10),
param3 bit
--etc
my new and old records are as follows
old version
---
composite primary key guid param1 param2 param3
CPK1 GUID1 4 'enabled' 0
CPK2 GUID2 5 'disabled' 0
CPK3 GUID3 6 'enabled' 1
new version
---
composite primary key guid param1 param2 param3
CPK1 GUID1 10 'enabled' 1
/*changed record*/
CPK2 GUID2 5 'disabled' 0
CPK3 GUID3 6 'disabled' 0
/*changed record*/
I am using binary_checksum(*) to find which ROWS are new ie they have
changed but I need to know which COLUMNS in each of those rows have
changed.There are about 100 columns in each row out of which only 3-4 will
change .
I am using SQL Server 2000.
Is there a way to extract just column names from each row that has changed?
Thanks.

tech77 (

tech77@.discussions.microsoft.com) writes:
> I am using binary_checksum(*) to find which ROWS are new ie they have
> changed but I need to know which COLUMNS in each of those rows have
> changed.There are about 100 columns in each row out of which only 3-4 will
> change .
> I am using SQL Server 2000.
> Is there a way to extract just column names from each row that has
> changed?
No, you need compare column by column.
And using binary_checksum is risky. Two versions of the same row could
have different checksum.
Are you doing this in a trigger? Is the purpose for auditing? Which
version of SQL Server are you using?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks for the reply.i m using sql 2000 and i m not doing this in a trigger.
I
am doing it in a procedure and all these calculations are done in a temp
table for the purpose of auditing changes to a column.
"Erland Sommarskog" wrote:

>

tech77 (

tech77@.discussions.microsoft.com) writes:
> No, you need compare column by column.
> And using binary_checksum is risky. Two versions of the same row could
> have different checksum.
> Are you doing this in a trigger? Is the purpose for auditing? Which
> version of SQL Server are you using?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||
Erland Sommarskog wrote:

>

tech77 (

tech77@.discussions.microsoft.com) writes:
>
>No, you need compare column by column.
>And using binary_checksum is risky. Two versions of the same row could
>have different checksum.
>
Erland,
I think you meant to say that two *different* versions of a row could
have the *same* checksum. You're correct to say that checksums are not
completely reliable indicators of change.
Steve Kass
Drew University

>Are you doing this in a trigger? Is the purpose for auditing? Which
>version of SQL Server are you using?
>
>|||

tech77 (

tech77@.discussions.microsoft.com) writes:
> thanks for the reply.i m using sql 2000 and i m not doing this in a
> trigger.I am doing it in a procedure and all these calculations are done
> in a temp table for the purpose of auditing changes to a column.
OK, you still need to run column by column. And avoid binary_checksum
or checksum.
Had you been on SQL 2005, I could have suggested alternative solutions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hi Thanks Erland and Steve for all your replies.
We would be migrating to SQL 2005 soon.Can you please tellme how to do it in
SQL 2005?
Thanks.
"Steve Kass" wrote:

>
> Erland Sommarskog wrote:
>
> Erland,
> I think you meant to say that two *different* versions of a row could
> have the *same* checksum. You're correct to say that checksums are not
> completely reliable indicators of change.
> Steve Kass
> Drew University
>
>|||

tech77 (

tech77@.discussions.microsoft.com) writes:
> We would be migrating to SQL 2005 soon.Can you please tellme how to do
> it in SQL 2005?
One thing I was thinking of was hash_bytes(), which should be more reliable
than binary_checksum. But I forgot that it does not operate on the entire
like binary_checksum(*) does, so it's probably not that useful.
However, what is an interesting option for auditing on SQL 2005, is the
XML datatype. The idea would be to have a single table where you would
have tablename, keyvalue1 and keyvalue2, and an after-image of the data
in an XML column. Then you would have regular audting columns as
when, whom, application and host name.
The trigger code can be standardised and you could write a simple
application that reads the log row a combination ot tablename and keyvalue,
and presented you for each entry what have canged. What's really

here, is that the presenting application would not need have much
knowledge about the data it presents.
This is an idea that we discussed in our shop when a prospective customer
asked for better auditing than we have today. The customer dropped out
for other reasons, but we might implement the idea when we come to
SQL 2005.
Of course, this method would not be very space-effective, but we prefer
this since developing and maintaining space-effective auditing is
tedious and expensive.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
I am using SQL Server express edition now.Can you please suggest your
alternative solution for finding changed row and columns?
Thanks.
"Erland Sommarskog" wrote:

>

tech77 (

tech77@.discussions.microsoft.com) writes:
> OK, you still need to run column by column. And avoid binary_checksum
> or checksum.
> Had you been on SQL 2005, I could have suggested alternative solutions.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||

tech77 (

tech77@.discussions.microsoft.com) writes:
> I am using SQL Server express edition now.Can you please suggest your
> alternative solution for finding changed row and columns?
I think that what I had in mind was to store an xml document of the
row in a table. This is possible, because in SQL 2005, you can use
FOR XML and receive the result in a variable or column of the xml data
type.
The audit table would look something like this:
CREATE TABLE audits (
ident int IDENTITY,
tablename sysname NOT NULL,
keyvalue1 sql_variant NOT NULL,
keyvalue2 sql_variant NULL,
moduser1 sysname NOT NULL
CONSTRAINT default_audit_moduser1 original_login(),
moduser2 sysname NOT NULL
CONSTRAINT default_audit_moduser2 SYSTEM_USER,
moddate datetime NOT NULL
CONSTRAINT default_audit_moddate getdate(),
hostname sysname NULL,
CONSTRAINT default_audit_hostname host_name(),
afterimage xml NULL,
CONSTRAINT pk_audit PRIMARY KEY (ident))
Thus, one table would hold the audit for many tables. The idea is that
if you could write a general application that reads all rows for a certain
table and keyvalue. The application would compare the XML documents, and
the present just the difference. The beauty of it is that the application
itself would not need to have any knowledge of the data model as such.
Some notes on details:
o The table only handle two-column keys. The assumption is that if you
have more columns in the key, they could still pass as the same record.
But add more keyvalue columns as needed.
o SYSTEM_USER and original_login() returns the same value in most cases,
but if EXECUTE AS is in force, they can yield different results.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Find blocking process

Hi,
we're having a problem with SQL 2000 and Opta 2000 JDBC driver
where there is large update running and at the same time,
read is blocked for a while.
We're looking for a way to catch this blocking process
and if it last more than 10 minutes, then email or send out a message.
I know sp_lock returns all current locks
but how do you know which one is blocking other processes?

Thanks for your help in advance.neo (second714@.hotmail.com) writes:
> we're having a problem with SQL 2000 and Opta 2000 JDBC driver
> where there is large update running and at the same time,
> read is blocked for a while.
> We're looking for a way to catch this blocking process
> and if it last more than 10 minutes, then email or send out a message.
> I know sp_lock returns all current locks
> but how do you know which one is blocking other processes?

The simplest way is to use sp_who. If a process is blocked, you will
see a non-zero value in the Blk column. This is the spid of the blocker.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

find backup directory

Hello!
MSQL2000:
I want to make db-backup (maintenance) but haven't enough space on the
discs that are physically in the machine. I have enough on the network, but
i can't choose an network-drive, when
i want to specify the backup directory.

What to do?
/Bjrn"bdjensen" <b.d.jensenMAPS_ON@.gmx.net> wrote in message news:<3fb22d61$0$95067$edfadb0f@.dread11.news.tele.dk>...
> Hello!
> MSQL2000:
> I want to make db-backup (maintenance) but haven't enough space on the
> discs that are physically in the machine. I have enough on the network, but
> i can't choose an network-drive, when
> i want to specify the backup directory.
> What to do?
> /Bjrn

Enterprise Manager only displays the local drives, but you can just
type in the UNC path: \\Server\Share\Folder.

Simon|||Create a backup device in enterprise manager. You can backup to network
locations this way.|||"bdjensen" <b.d.jensenMAPS_ON@.gmx.net> wrote in message
news:3fb22d61$0$95067$edfadb0f@.dread11.news.tele.d k...
> Hello!
> MSQL2000:
> I want to make db-backup (maintenance) but haven't enough space on the
> discs that are physically in the machine. I have enough on the network,
but
> i can't choose an network-drive, when
> i want to specify the backup directory.

Note, as others have said, you can use UNC paths. However, SQL Server Agent
must be running in a user context that can map those drives w/o human
intervention.

> What to do?
> /Bjrn

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
Thanks
Hi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
ThanksHi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
ThanksHi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

find available time slot

I have a table that looks like:
CREATE TABLE [dbo].[UserAgenda] (
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
) ON [PRIMARY]
GO
and I would like to find a time gap for the amount of time I am looking
for.
For example, I want the next available hour in the list or the next
available 15 minutes.
Any help would be appreciated.
Thanks
Richard MeetzeAm I correct that "next available" means a period of time that won't
overlap the period between StartTime and EndTime on any row in the
table? Try this:
DECLARE @.m INTEGER
SET @.m = 10
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
David Portas
SQL Server MVP
--|||David - you are correct, thanks for the help.
I have another question if you or anyone else it up for it.
Using the same table schema as above, I have the following data:
Start End
2005-01-01 08:15:00.000 2005-01-01 08:45:00.000
2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
Now I want to place an item in from 8:30 to 8:45 and I want my end
result to look like:
Start End
2005-01-01 08:15:00.000 2005-01-01 08:30:00.000
2005-01-01 08:30:00.000 2005-01-01 08:45:00.000
2005-01-01 08:45:00.000 2005-01-01 09:00:00.000
2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
I had to insert the new time in a current time and split the other time
to two parts and make it fit. Does make since?
Thanks
Richard Meetze|||David,
I think will should check for existence of rows between the matching ones.
Somethnig like:
...
and not exists (select * from UserAgenda as c where c.starttime > a.endtime
and c.endtime < b.starttime)
Example:
create table UserAgenda (
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL
)
insert into UserAgenda values('2005-02-23T08:30:00', '2005-02-23T08:35:00')
insert into UserAgenda values('2005-02-23T08:36:00', '2005-02-23T08:46:00')
insert into UserAgenda values('2005-02-23T08:47:00', '2005-02-23T08:57:00')
DECLARE @.m INTEGER
SET @.m = 10
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
and not exists (select * from UserAgenda as c where c.starttime > a.endtime
and c.endtime < b.starttime)
drop table UserAgenda
go
AMB
"David Portas" wrote:

> Am I correct that "next available" means a period of time that won't
> overlap the period between StartTime and EndTime on any row in the
> table? Try this:
> DECLARE @.m INTEGER
> SET @.m = 10
> SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
> FROM UserAgenda AS A
> LEFT JOIN UserAgenda AS B
> ON A.endtime <= B.starttime
> WHERE DATEDIFF(MI,A.endtime,
> COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
> --
> David Portas
> SQL Server MVP
> --
>|||See if it hepls you
create table UserAgenda
(
StartTime datetime not null,
EndTime datetime not null
)
insert into UserAgenda(StartTime,EndTime)values ('20000610 10:00','20000610
10:15')
insert into UserAgenda(StartTime,EndTime)values ('20000610 10:30','20000610
10:50')
insert into UserAgenda(StartTime,EndTime)values ('20000610 11:00','20000610
11:25')
insert into UserAgenda(StartTime,EndTime)values ('20000610 11:45','20000610
11:55')
insert into UserAgenda(StartTime,EndTime)values ('20000610 12:57','20000610
13:00')
insert into UserAgenda(StartTime,EndTime)values ('20000610 13:15','20000610
13:30')
insert into UserAgenda(StartTime,EndTime)values ('20000610 14:04','20000610
14:40')
SELECT
StartTime,
ISNULL(
(SELECT MIN(EndTime)
FROM UserAgenda AS S3
WHERE S3.StartTime >= S1.StartTime
AND ISNULL(
DATEDIFF(
minute,
S3.EndTime,
(SELECT MIN(StartTime)
FROM UserAgenda AS S4
WHERE S4.StartTime > S3.EndTime)), 15) <= 15),
EndTime) AS EndTime
FROM UserAgenda AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTime)
FROM UserAgenda AS S2
WHERE S2.EndTime < S1.StartTime),S1.StartTime),15)<= 15
<meetze@.gmail.com> wrote in message
news:1109184358.837536.30570@.f14g2000cwb.googlegroups.com...
> David - you are correct, thanks for the help.
> I have another question if you or anyone else it up for it.
> Using the same table schema as above, I have the following data:
> Start End
> 2005-01-01 08:15:00.000 2005-01-01 08:45:00.000
> 2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
> 2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
> 2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
> 2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
> 2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
> Now I want to place an item in from 8:30 to 8:45 and I want my end
> result to look like:
> Start End
> 2005-01-01 08:15:00.000 2005-01-01 08:30:00.000
> 2005-01-01 08:30:00.000 2005-01-01 08:45:00.000
> 2005-01-01 08:45:00.000 2005-01-01 09:00:00.000
> 2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
> 2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
> 2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
> 2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
> 2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
> I had to insert the new time in a current time and split the other time
> to two parts and make it fit. Does make since?
> Thanks
> Richard Meetze
>

find any character in a string that is a lower case

in SQL Server 7, How can I find out if a database field has any lower case
character in the string. Thanks!you can create a temp table w/ the letters of the alphabet in it and
search that field IN #temp table.
or you could find a length of the string and then substring letter by
letter and search the ASCII values for each lowercase letter against
the substring letter.|||Thanks! What I did is to find the sum of the ASCII values for the whole
string and the sum of ASCIIs for upper(string) at the same time. If they are
not equal, there is a lower case character.
"GlennThomas5" wrote:

> you can create a temp table w/ the letters of the alphabet in it and
> search that field IN #temp table.
> or you could find a length of the string and then substring letter by
> letter and search the ASCII values for each lowercase letter against
> the substring letter.
>

find any character in a string that is a lower case

in SQL Server 7, How can I find out if a database field has any lower case
character in the string. Thanks!you can create a temp table w/ the letters of the alphabet in it and
search that field IN #temp table.
or you could find a length of the string and then substring letter by
letter and search the ASCII values for each lowercase letter against
the substring letter.|||Thanks! What I did is to find the sum of the ASCII values for the whole
string and the sum of ASCIIs for upper(string) at the same time. If they are
not equal, there is a lower case character.
"GlennThomas5" wrote:
> you can create a temp table w/ the letters of the alphabet in it and
> search that field IN #temp table.
> or you could find a length of the string and then substring letter by
> letter and search the ASCII values for each lowercase letter against
> the substring letter.
>

find any character in a string that is a lower case

in SQL Server 7, How can I find out if a database field has any lower case
character in the string. Thanks!
you can create a temp table w/ the letters of the alphabet in it and
search that field IN #temp table.
or you could find a length of the string and then substring letter by
letter and search the ASCII values for each lowercase letter against
the substring letter.
|||Thanks! What I did is to find the sum of the ASCII values for the whole
string and the sum of ASCIIs for upper(string) at the same time. If they are
not equal, there is a lower case character.
"GlennThomas5" wrote:

> you can create a temp table w/ the letters of the alphabet in it and
> search that field IN #temp table.
> or you could find a length of the string and then substring letter by
> letter and search the ASCII values for each lowercase letter against
> the substring letter.
>

Find and Replace.

Does anybody know how to search through all stored procedures (in SQL Server 2000) and find and replace text without having to open each one individually in Query Analyzer and doing so one at a time?

That would be so time consuming. I want to be able to change my table names, but I have so many stored procedures allready using the old names. To go and find each name and replacing them is a task I don't want to even try and do.

Thank you to whomever can help.

Alecgo to enterprise manager > your database >
stored procedures > select all >
right click > All Tasks > Generate SQL Script >
click the Options Tab > check the box "Script Object Permission" >
hit the OK button and save this as a file.

then you can open this file up and do search and replace as you wish.
the file should contain sp drop statements followed by CREATE procedure statements

after you have masaged the file run it in QA
I recommend backing up your database first.

Find and Replace string value in a stored procedure.

Not sure if there is a way to do this programmatically. I am using SQL
Server 2005 and I need to find a specific string value in a stored
procedure and replace that string value with some other value.
I can perform the "Find" part of the process by querying the
Information_Schema.Routines view but not sure there is a way to
perform the replace, essentially ALTERING the stored procedure. Is
there a command I can use that can do this? I essentially want to do
a batch Find and Replace type process.
Thanks in advance!Safest way is to use the code files you have stored in your source code
control system. Check them all out, do a find/replace in files command
(using a tool such as Search and Replace), check them back in. Compile the
ones that had a replacement. S&R can provide a file listing that can be
used to drive a compile process using sqlcmd or numerous other mechanisms.
If you don't have a system like the above, take the time to set one up! :-)
You can easily script all sprocs to separate files using SSMS or many other
3rd party tools. I like ApexSQL's Script.
<lgalumbres@.gmail.com> wrote in message
news:1191591082.224636.49880@.o80g2000hse.googlegroups.com...
> Not sure if there is a way to do this programmatically. I am using SQL
> Server 2005 and I need to find a specific string value in a stored
> procedure and replace that string value with some other value.
> I can perform the "Find" part of the process by querying the
> Information_Schema.Routines view but not sure there is a way to
> perform the replace, essentially ALTERING the stored procedure. Is
> there a command I can use that can do this? I essentially want to do
> a batch Find and Replace type process.
> Thanks in advance!
>

find and replace

To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?

For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.

So you want to search all user tables for the value to be deleted? It's hard to control which tables to be search and it will be a huge workload when there are many tables. If you just want to maintenance the Reference Integrity, why not use constraints? You can create PK/FK between the tables, and cascading changes on the referencing columns. For more information about this, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_92ib.asp

find and replace

how do i write a query that will go through all the rows of a text field and
replace the search word with a new word.
like the find and replace feature in MS Access
REPLACE "ABC" WITH "CBS" IN DBTABLE.DBTEXT
Thanks,
AaronHi,
Use Update statement with Replace function in SET clause. This will work out
if ur data type is
Varchar/Char/Nchar,Nvarchar.
Thanks
Hari
SQL Server MVP
"Aaron" <kuya789@.yahoo.com> wrote in message
news:uvdHyxNOFHA.3396@.TK2MSFTNGP10.phx.gbl...
> how do i write a query that will go through all the rows of a text field
> and replace the search word with a new word.
> like the find and replace feature in MS Access
> REPLACE "ABC" WITH "CBS" IN DBTABLE.DBTEXT
> Thanks,
> Aaron
>|||Could you give me an example?
"Hari Pra

" <hari_pra

_k@.hotmail.com> wrote in message
news:Oormu$NOFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Use Update statement with Replace function in SET clause. This will work
> out if ur data type is
> Varchar/Char/Nchar,Nvarchar.
> Thanks
> Hari
> SQL Server MVP
> "Aaron" <kuya789@.yahoo.com> wrote in message
> news:uvdHyxNOFHA.3396@.TK2MSFTNGP10.phx.gbl...
>|||Aaron
CREATE TABLE #Test
(
col VARCHAR(10)
)
INSERT INTO #Test VALUES ('FDABC')
UPDATE #Test SET col=REPLACE(col,'ABC','CBS')
--or if you know the start point and length of the string
UPDATE #Test SET col=STUFF(col,3,3,'CBS')
SELECT * FROM #Test
"Aaron" <kuya789@.yahoo.com> wrote in message
news:eSVOunOOFHA.2520@.tk2msftngp13.phx.gbl...
> Could you give me an example?
>
> "Hari Pra

" <hari_pra

_k@.hotmail.com> wrote in message
> news:Oormu$NOFHA.904@.tk2msftngp13.phx.gbl...
field
>

Find and drop a constraint before dropping the column

Hi all.
I want to check and find the name of the constraint and drop it before I
drop the column. Or, just drop the column without the errormessage that
there are depending objects. Any ideas?
Now I have to drop the column, look at the errormessage, copy/paste into a
drop constraint and then drop the column again.
Thanx all.
ALTER TABLE Tablename
DROP CONSTRAINT DF__Tablename__ColumnName__21D6CC45
GO
ALTER TABLE Tablename
DROP COLUMN ColumnName
goYou can use sp_help and sp_helpconstraint.
can view PK, FK,ID,CHECK etc.
"Geir Holme"?? ??? ??:

> Hi all.
> I want to check and find the name of the constraint and drop it before I
> drop the column. Or, just drop the column without the errormessage that
> there are depending objects. Any ideas?
> Now I have to drop the column, look at the errormessage, copy/paste into a
> drop constraint and then drop the column again.
> Thanx all.
>
> ALTER TABLE Tablename
> DROP CONSTRAINT DF__Tablename__ColumnName__21D6CC45
> GO
> ALTER TABLE Tablename
> DROP COLUMN ColumnName
> go
>
>

Find and delete reversed key pairs.

Hi!

I have a table with only two columns, which contain some city codes in the form of AAA, BBB. What I need to do is to find reversed key pairs in the same table and get rid of them so only one pair stays. In example, I have:

AAA, BBB

CCC, DDD

BBB, AAA

EEE, FFF

FFF, EEE

DDD, CCC

AAA, BBB and BBB, AAA mean the same to me, so I would like to leave only one of them. Do you have any idea on how to achieve this? I tried to use a cursor (yeah, I know cursors are bad, but this is a task I will most likely run once or every few weeks), but the table has over 130k records and it looks like I need at least a few hours for the cursor to complete it's work. Here's what I did:

Code Snippet

declare
@.origCity char(3)
,@.destCity char(3)

declare crs_cities cursor local fast_forward for
select
origCity, destCity
from
dbo.airports
order by
origCity

open crs_cities

fetch next from crs_cities
into @.origCity, @.destCity

while @.@.fetch_status = 0
begin
-- Delete rows having the same city pair in reverse order.
delete from
dbo.airports
where
destCity = @.origCity
and
origCity = @.destCity

fetch next from crs_cities
into @.origCity, @.destCity
end

close crs_cities
deallocate crs_cities

I could also add an autoincrement column before the pairs and use a while loop instead of a cursor, but there must be some SQL query to do it the proper way. It may be a whole procedure, whatever.

Thanks.

delete from airports
where origCity>destCity
and exists (select * from airports a2
where a2.origCity=airports.destCity
and a2.destCity=airports.origCity)
|||You also need to adjust your insert code so that "duplicate" records do not get inserted in the first place.|||

I cannot believe it was so simple! x_x

If I may ask - why origCity>destCity and not something else. I don't get it.

Kent Waldrop 2007 Mar wrote:
You also need to adjust your insert code so that "duplicate" records do not get inserted in the first place.

This was some data imported from a CSV I got from a client, so it's not a case. Wink

|||

pawel.krakowiak wrote:

If I may ask - why origCity>destCity and not something else. I don't get it.

You need to ensure that the operation only deletes one record of each pair (without that condition it would have deleted both records of the pair). The condition arbitrarily selects the records where origCity sorts after destCity to remove. Mark could equally well have used origCity < destCity and removed the other record of the pair.

|||Thought so... I never used such construct (I mean, it depends on the context) before, though. Good to know.

Find an associated user

I am trying to copy a database that is on a Windows 2000 box to one
that is on Windows 2003. Is there a way to export a list of server
login names? I am having problems with one I am trying to create where
on the old server using Teratrax I can see that it is an associated
user Name, but I don't know which user it is associated with. Is there
a command that can tell me which user the account is associated with?
It definitely isn't a domain user and isn't a local account. It says
that the user is a SQl user but I can't seem to find where the
association is.
hi Mark,
"Mark Constant" <constants@.mix-net.net> ha scritto nel messaggio
news:ce43fdea.0412011310.6ac9ef82@.posting.google.c om
> I am trying to copy a database that is on a Windows 2000 box to one
> that is on Windows 2003. Is there a way to export a list of server
> login names? I am having problems with one I am trying to create where
> on the old server using Teratrax I can see that it is an associated
> user Name, but I don't know which user it is associated with. Is there
> a command that can tell me which user the account is associated with?
> It definitely isn't a domain user and isn't a local account. It says
> that the user is a SQl user but I can't seem to find where the
> association is.
SET NOCOUNT ON
USE TEMPDB
PRINT 'adding SQL Server Login [x] to [' + DB_NAME() + '] as [x_user] in the
[db_datareader] database role'
EXEC sp_adduser 'x', 'x_user', 'db_datareader'
PRINT 'grandting [x_user] in [' + DB_NAME() + '] database [db_datawriter]
database role membership'
EXEC sp_addrolemember @.rolename = 'db_datawriter' , @.membername = 'x_user'
PRINT 'listing all [' + DB_NAME() + '] database users'
EXEC sp_helpuser
PRINT 'listing [' + DB_NAME() + '] database [x_user] user''s database role
membership'
EXEC sp_helpuser 'x_user'
PRINT 'Dropping [x_user] user from [' + DB_NAME() + '] database'
EXEC sp_dropuser 'x_user'
which results to
adding SQL Server Login [x] to [tempdb] as [x_user] in the [db_datareader]
database role
Granted database access to 'x'.
'x_user' added to role 'db_datareader'.
grandting [x_user] in [tempdb] database [db_datawriter] database role
membership
'x_user' added to role 'db_datawriter'.
listing all [tempdb] database users
UserName GroupName LoginName DefDBName UserID SID
-- -- -- -- -- --
----
dbo db_owner sa master 1 0x01
x_user db_datareader x a 5
0x67F945E170B2574AA8DCB909B68D46A9
x_user db_datawriter x a 5
0x67F945E170B2574AA8DCB909B68D46A9
listing [tempdb] database [x_user] user's database role membership
UserName GroupName LoginName DefDBName UserID SID
-- -- -- -- -- --
----
x_user db_datareader x a 5
0x67F945E170B2574AA8DCB909B68D46A9
x_user db_datawriter x a 5
0x67F945E170B2574AA8DCB909B68D46A9
Dropping [x_user] user from [tempdb] database
User has been dropped from current database.
as you can see you can inspect the result of sp_helpuser system store
precedure
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_sp_help_45o2.asp) that reports, as long as the user name, the corresponding
Login..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

find an article

There was an article that Ive come across and not even aware of what issue
it pertains to..
All i remember is that it talks about perfmon and based upon the ecid value
or something like that in sysprocesses, it then states to go to the thread
counter. It was pretty interesting and it was to debug something.. Does
anyone recollect this ? Not too sure if i read it at the
sql-server-performance.com site or someplace else...
maybe from the Online Seminar from PSS below
http://support.microsoft.com/default...b;en-us;838622
http://support.microsoft.com/default...Fwct041504.asp
Andy
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e%23rJovGYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> There was an article that Ive come across and not even aware of what issue
> it pertains to..
> All i remember is that it talks about perfmon and based upon the ecid
value
> or something like that in sysprocesses, it then states to go to the thread
> counter. It was pretty interesting and it was to debug something.. Does
> anyone recollect this ? Not too sure if i read it at the
> sql-server-performance.com site or someplace else...
>
>

find an article

There was an article that Ive come across and not even aware of what issue
it pertains to..
All i remember is that it talks about perfmon and based upon the ecid value
or something like that in sysprocesses, it then states to go to the thread
counter. It was pretty interesting and it was to debug something.. Does
anyone recollect this ? Not too sure if i read it at the
sql-server-performance.com site or someplace else...maybe from the Online Seminar from PSS below
http://support.microsoft.com/default.aspx?scid=kb;en-us;838622
http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fwebcasts%2Fen%2Ftranscripts%2Fwct041504.asp
Andy
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e%23rJovGYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> There was an article that Ive come across and not even aware of what issue
> it pertains to..
> All i remember is that it talks about perfmon and based upon the ecid
value
> or something like that in sysprocesses, it then states to go to the thread
> counter. It was pretty interesting and it was to debug something.. Does
> anyone recollect this ? Not too sure if i read it at the
> sql-server-performance.com site or someplace else...
>
>

find an article

There was an article that Ive come across and not even aware of what issue
it pertains to..
All i remember is that it talks about perfmon and based upon the ecid value
or something like that in sysprocesses, it then states to go to the thread
counter. It was pretty interesting and it was to debug something.. Does
anyone recollect this ? Not too sure if i read it at the
sql-server-performance.com site or someplace else...maybe from the Online Seminar from PSS below
http://support.microsoft.com/defaul...kb;en-us;838622
ranscripts%2Fwct041504.asp" target="_blank">http://support.microsoft.com/defaul...2Fwct041504.asp
Andy
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e%23rJovGYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> There was an article that Ive come across and not even aware of what issue
> it pertains to..
> All i remember is that it talks about perfmon and based upon the ecid
value
> or something like that in sysprocesses, it then states to go to the thread
> counter. It was pretty interesting and it was to debug something.. Does
> anyone recollect this ? Not too sure if i read it at the
> sql-server-performance.com site or someplace else...
>
>

find all users in a sql server group

Hi There,
is it possible find all users in a sql server group, I'm not administrator,
I can use query analyser to run scripts that's it.
Thanks
GaneshUse sp_helpgroup system procedure, for instance:
EXEC sp_helpgroup 'db_owner'
Anith

find all triggers

How do I query the schema views (preferably) or sys tables to find all
triggers on my db?
tia
chrisDisplays the name of the table and the triggers:
Select c.name,a.name
From sysobjects a, sysdepends b, sysobjects c
Where a.id = b.id
And b.depid = c.id and a.type = 'TR' and c.type = 'U'
'U' defines tables, views are not incuded here, for that, leave out the last
part
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BD6E22CA-A815-4CD1-AD15-590E6C168146@.microsoft.com...
> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
> tia
> chris|||Select name TriggerName,
object_name(parent_obj) TableName
from sysobjects
Where type = 'TR'
"Chris" wrote:

> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
> tia
> chris|||Hi Chris,
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:BD6E22CA-A815-4CD1-AD15-590E6C168146@.microsoft.com...
> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
Try this:
select
trig.name as [Trigger Name],
tab.name as [Table Name],
case
when ObjectProperty( trig.id, 'ExecIsInsteadOfTrigger') = 1 then 'INSTEAD OF
' else 'FOR ' end
+ substring (
case when ObjectProperty( trig.id, 'ExecIsInsertTrigger') = 1 then ',
INSERT' else '' end +
case when ObjectProperty( trig.id, 'ExecIsUpdateTrigger') = 1 then ',
UPDATE' else '' end +
case when ObjectProperty( trig.id, 'ExecIsDeleteTrigger') = 1 then ',
DELETE' else '' end
, 3, 100) as [Trigger for]
from sysobjects as trig
inner join sysobjects as tab on tab.id = trig.parent_obj
where trig.type = 'TR'
order by tab.name
Or this:
select
object_name(parent_obj) as TableName,
name as TriggerName,
case(OBJECTPROPERTY(id, 'ExecIsTriggerDisabled'))
when 0 then 'YES'
when 1 then 'NO' end as Enable,
case(OBJECTPROPERTY(id, 'ExecIsInsertTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Insert],
case(OBJECTPROPERTY(id, 'ExecIsUpdateTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Update],
case(OBJECTPROPERTY(id, 'ExecIsDeleteTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Delete],
case(OBJECTPROPERTY(id, 'ExecIsAfterTrigger'))
when 0 then 'Instead of'
when 1 then 'After' end as [Type],
crdate as CreationDate
from
sysobjects
where
type = 'TR'
order by
object_name(parent_obj), name

> tia
> chris
HTH,
Andrea - www.absistemi.it

Find all the objects(stored procedures) that uses a particular view

Hi,
I need to modify a View. But I don't know how many objects(stored procedure)
in SQL Server uses this view.
Is there a where to find out which objects use this particular view.
I can't do this manually because my DB contains 100's of stored procedures
Thanks
Kiranyou can check the dependancies in EM, or in query analyzer use sp_depends
sp_depends
Displays information about database object dependencies (for example, the
views and procedures that depend on a table or view, and the tables and
views that are depended on by the view or procedure). References to objects
outside the current database are not reported.
Syntax
sp_depends [ @.objname = ] 'object'
Arguments
[@.objname =] 'object'
The database object to examine for dependencies. The object can be a table,
view, stored procedure, or trigger. object is nvarchar(776), with no
default.
Return Code Values
0 (success) or 1 (failure)
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to modify a View. But I don't know how many objects(stored
procedure)
> in SQL Server uses this view.
> Is there a where to find out which objects use this particular view.
> I can't do this manually because my DB contains 100's of stored procedures
> Thanks
> Kiran
>|||Hi Simon,
The "sp_depends view_name" returns the list of column names that are in the
view and the related object name, type etc.....
I want to get the names of object that uses this View.
For eg. I have a view vew_employees. There are 4 stored procedures that uses
this view vew_employees.
I need something that will return me the names of these 4 sp.s
Thanks
Kiran
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> you can check the dependancies in EM, or in query analyzer use sp_depends
> sp_depends
> Displays information about database object dependencies (for example, the
> views and procedures that depend on a table or view, and the tables and
> views that are depended on by the view or procedure). References to
> objects
> outside the current database are not reported.
> Syntax
> sp_depends [ @.objname = ] 'object'
> Arguments
> [@.objname =] 'object'
> The database object to examine for dependencies. The object can be a
> table,
> view, stored procedure, or trigger. object is nvarchar(776), with no
> default.
> Return Code Values
> 0 (success) or 1 (failure)
>
> "Kiran Kumar" <nospam@.spam.com> wrote in message
> news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I need to modify a View. But I don't know how many objects(stored
> procedure)
>> in SQL Server uses this view.
>> Is there a where to find out which objects use this particular view.
>> I can't do this manually because my DB contains 100's of stored
>> procedures
>> Thanks
>> Kiran
>>
>|||Assuming your view name is somewhat unique, this will give you an idea:
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%view_name%'
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> > you can check the dependancies in EM, or in query analyzer use
sp_depends
> >
> > sp_depends
> > Displays information about database object dependencies (for example,
the
> > views and procedures that depend on a table or view, and the tables and
> > views that are depended on by the view or procedure). References to
> > objects
> > outside the current database are not reported.
> >
> > Syntax
> > sp_depends [ @.objname = ] 'object'
> >
> > Arguments
> > [@.objname =] 'object'
> >
> > The database object to examine for dependencies. The object can be a
> > table,
> > view, stored procedure, or trigger. object is nvarchar(776), with no
> > default.
> >
> > Return Code Values
> > 0 (success) or 1 (failure)
> >
> >
> >
> > "Kiran Kumar" <nospam@.spam.com> wrote in message
> > news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> >> Hi,
> >>
> >> I need to modify a View. But I don't know how many objects(stored
> > procedure)
> >> in SQL Server uses this view.
> >>
> >> Is there a where to find out which objects use this particular view.
> >>
> >> I can't do this manually because my DB contains 100's of stored
> >> procedures
> >>
> >> Thanks
> >> Kiran
> >>
> >>
> >
> >
>|||But it does return those 4 procs - within the name column of the output. Is
this not what you are looking for?
In enterprise manager, right click the view, go to "all tasks", and click
"display dependancies...". This will show you the same thing
"Kiran Kumar" <nospam@.spam.com> wrote in message
news:e4vvuU1HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Hi Simon,
> The "sp_depends view_name" returns the list of column names that are in
the
> view and the related object name, type etc.....
> I want to get the names of object that uses this View.
> For eg. I have a view vew_employees. There are 4 stored procedures that
uses
> this view vew_employees.
> I need something that will return me the names of these 4 sp.s
> Thanks
> Kiran
> "Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
> news:eiHfFM1HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> > you can check the dependancies in EM, or in query analyzer use
sp_depends
> >
> > sp_depends
> > Displays information about database object dependencies (for example,
the
> > views and procedures that depend on a table or view, and the tables and
> > views that are depended on by the view or procedure). References to
> > objects
> > outside the current database are not reported.
> >
> > Syntax
> > sp_depends [ @.objname = ] 'object'
> >
> > Arguments
> > [@.objname =] 'object'
> >
> > The database object to examine for dependencies. The object can be a
> > table,
> > view, stored procedure, or trigger. object is nvarchar(776), with no
> > default.
> >
> > Return Code Values
> > 0 (success) or 1 (failure)
> >
> >
> >
> > "Kiran Kumar" <nospam@.spam.com> wrote in message
> > news:%23zq%23FG1HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> >> Hi,
> >>
> >> I need to modify a View. But I don't know how many objects(stored
> > procedure)
> >> in SQL Server uses this view.
> >>
> >> Is there a where to find out which objects use this particular view.
> >>
> >> I can't do this manually because my DB contains 100's of stored
> >> procedures
> >>
> >> Thanks
> >> Kiran
> >>
> >>
> >
> >
>

Find all the objects(stored procedures) that uses a particular view

find all the employees under one manager (was "Need Help with Query")

I have an employee table with manager id and employee ids , i need to find all the employee ids for a manager id . Each employee can be a manager in turn . So I need to find all the employees under one manager and if any of the employee is in turn a manager , i need to find the employees under him as well .

The table structure is defined and i cannot edit it .

Please let me know if we could have a single query to do this .

Thank you
kishoreYou don't state what version of SQL Server you are using. New to SQL Server 2005 is Common Table Expressions (CTEs). An article on MSDN describes exactly what you are looking for: Recursive Queries Using Common Table Expressions (http://msdn2.microsoft.com/en-us/library/ms186243.aspx).

I'm afraid this is somewhat more tricky on SQL Server 2000.|||I need to support SQL server 2000 as well as 2005 ,also other Database like oracle , Db2 and sybase. Apart from Common Table Expression , is there any way i can do it .|||Well, the code won't be identical on the various platforms anyway. CTEs are supported on SQL Server 2005 and DB2, and perhaps the latest version of Sybase as well. It is a part of the SQL Standard since SQL:1999, so I would suggest using it where possible. For the rest you could create a temporary table, and use pretty much the same idea as in the CTE:

1. Insert all without a parent (top level)
2. Insert all whose having those in 1 as parent
3. Recursively insert all whose having their parent inserted, but are not inserted themselves yet.

I know, it's not very pretty code, but it works. Hope you got my idea :)|||SELECT e.known_as_and_surname
AS 'Employee'
,m.known_as_and_surname
AS 'Manager'
FROM employee AS e
LEFT OUTER JOIN employee AS m
ON m.employee_number = e.manager_number

Any good to you?
Self-join genius courtesy of Rudy at www.r937.com :beer:|||Any good to you?
That would be nice for 2 levels (manager and employee).
Triumph wants a top person's subordinates from 1, 2, x levels down.

If there is a fixed number of levels you can union that number (minus one) of select statements. If not, the temporary table that roac describes would be useful.

<edit>
Congratulations!|||roac approach would work i guess , but i was looking for a solution where in a single query would do the trick , but i guess its not going to be the case . I needed this solution to improve performance .|||no single query is ever going to work the same in sql server 2000, sql server 2005, oracle, db2, and sybase

if you are writing an application that "abstracts" the database layer, you are never going to achieve your result by trying to abstract the sql

instead, you need to abstract the information request, and write specific sql modules for each database -- in this case, CONNECT BY for oracle, CTEs for those that support it (it's part of the sql standard), a recursive call for other databases, etc.|||Thanks to all for helping me. I think i will do the recurvise calls using Java .|||Well good luck!

I'd love to see a solution to this if/when you get one ;)|||Thanks to all for helping me. I think i will do the recurvise calls using Java .
Ugh.
You would get excellent performance using the algorithm suggested by ROAC. I'd bet it would beat any recursive algorithm hands down. Recursion requires a separate call for each item in the hierarchy, while ROAC's suggestion only requires one call for each level in the hierarchy.|||I noticed the thread changed title... This simple query solves it as the title asks ;)
To find all the employees under ONE manager:

SELECT e.known_as_and_surname AS 'Employee'
FROM employee AS e
LEFT OUTER JOIN employee AS m
ON m.employee_number = e.manager_number
WHERE m.known_as_and_surname = '<InsertNameOfManager>'|||Without editing the layout of the table you're going to have difficulty getting a work around. If you know the max number of levels then you could hack it with a single query (based on rudy's example) :

SELECT
e1.name AS 'Lvl1',
e2.name AS 'Lvl2',
e3.name AS 'Lvl3',
e4.name AS 'Lvl4',
e5.name AS 'Lvl5',
FROM employee AS e1
LEFT OUTER JOIN employee AS e2 ON e2.employee_number = e1.manager_number
LEFT OUTER JOIN employee AS e3 ON e3.employee_number = e2.manager_number
LEFT OUTER JOIN employee AS e4 ON e4.employee_number = e3.manager_number
LEFT OUTER JOIN employee AS e5 ON e5.employee_number = e4.manager_number|||Queston : does MSSQL (2000) support recursive procedures?|||even if there is no maximum number of levels, you could still use the 4-way self-join to show just 4 levels of subtree, with links at each bottom level node if it has any further levels below it

and of course clicking on one of the links returns up 4 lower levels below that node, so in effect it "recurses" down the tree but it is the user driving the process 4 levels at a time

usually, any tree that is large enough to have "unlimited" levels is at the same time also too large to allow the complete display of the entire tree, so recursion would be neither practical nor desirable anyway

the way i see it ;)|||Rudy you have a point, however if this is for report generation and there are a lot of employees/managers then full recursion is going to be needed. Or you're planning on making a nice big tree heirarchy using some fancy graphical functionality then i suspect returning the full set will still be beneficial.

find all tables that a table is related to

how can i find all the names of tables that a specific table is related to in tsql?

sp_depends

Displays information about database object dependencies (for example, the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure). References to objects outside the current database are not reported.

Examples

This example lists the database objects that depend on the Customers table.

USE NorthwindEXEC sp_depends 'Customers'

|||

Assuming you have referential integrity in place you can also query the sys.sysforeignkeys view in 2005 or the sysforeignkeys table in 2000. Here is a query returning all table associations (2005 syntax, to use in 2000 remove "sys." prefix):

SELECT

o.name [parentTable]

,o2.name [childTable]

FROM

sys.sysobjects o

INNER JOIN sys.sysforeignkeys fk ON fk.rkeyid = o.id

INNER JOIN sys.sysobjects o2 ON o2.id = fk.fkeyid AND o2.id <> o.id

WHERE

o.name = 'myTable'

Find all tables in database that are empty.

I need to find all the tables in a database that are empty (have nothing in
them). I know how to do it individually but can I do a mass search for
tables without data.
Thanks,EXEC sp_msforeachtable 'IF NOT EXISTS (SELECT * FROM ?) PRINT ''?'''
This proc is undocumented so don't use it in production code.
David Portas
SQL Server MVP
--|||If your statistics are up2date, you could also do this:
select object_name(id)
from sysindexes
where rowcnt<=0
and indid in (0,1)
and objectproperty(id,'isUserTable')=1
-oj
"Richard Thayne" <richard.thayne@.data2logistics.com> wrote in message
news:%23Knwk1UFFHA.3972@.TK2MSFTNGP15.phx.gbl...
>I need to find all the tables in a database that are empty (have nothing in
>them). I know how to do it individually but can I do a mass search for
>tables without data.
> Thanks,
>

find all sps where a field is used

In my database i want list of all stored procedures where i have used a
specific field.
Is there any way to do this.. any system sp which does thisYou have to search the prcedure definition for that, because AFAIK
there is no system binding or something like this where the information
is stored which columns are used. So the approach could be SELECT
Routine_name from INFORMATION_SCHEMA.Routines Where Routine_definition
like '%SomeValue%'
HTH, jens Suessmeyer.|||The information isn't stored in any system table, except for the source code
for the procedure
(syscomments). So you can use a LIKE predicate against the text column in sy
scomments, but be
careful as syscomments can break the line (use several rows for a procedure)
in the middle of a word
(at least it used to, perhaps newer versions are better).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vikram" <aa@.aa> wrote in message news:OmDLgniAGHA.204@.TK2MSFTNGP15.phx.gbl...d">
> In my database i want list of all stored procedures where i have used a
> specific field.
> Is there any way to do this.. any system sp which does this
>|||This might not be very pretty, but it should do the job:
CREATE TABLE #Depends
([Name] nvarchar(128),[type]nvarchar(128))
INSERT INTO #Depends
EXEC SP_Depends 'Table1'
SELECT [Name] from #depends
WHERE [type] = 'stored procedure'
AND OBJECT_ID([Name]) IN (Select [id] from syscomments where [text]
LIKE '%FieldName%')
Drop Table #depends
Markus|||(The following applies to SQL 2000. I haven't tried it in SQL 2005.)
I'm assuming that enterprise manager uses sp_depends to display dependancy
information. If this is true, your suggestion will only work if objects are
created in the correct sequence.
For example, it works if:
1. Create Table1.
2. Create proc MyProc1 that select Data from Table1.
If you drop table1 and recreate it, dependancy information is lost and will
not be shown. Same holds true for stored procedures. If you create a store
d
procedure that references another stored procedure that doesn't exist yet,
you will not have dependancy information even after you create the other
stored procedure.
IMHO, the only sure fired way is to script the database to a text file and
search it for the column name and/or table names that you are interested in.
Also, if any view, stored procedure, or user-defined function uses an *,
then you can't search by column name.
Hope that helps,
Joe
"m.bohse@.quest-consultants.com" wrote:

> This might not be very pretty, but it should do the job:
> CREATE TABLE #Depends
> ([Name] nvarchar(128),[type]nvarchar(128))
> INSERT INTO #Depends
> EXEC SP_Depends 'Table1'
> SELECT [Name] from #depends
> WHERE [type] = 'stored procedure'
> AND OBJECT_ID([Name]) IN (Select [id] from syscomments where [text]
> LIKE '%FieldName%')
> Drop Table #depends
> Markus
>

Sunday, February 26, 2012

sp_depends

Blog Archive

About Me