Discover relationships between existing tables of a database

Hello Friends,
I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules.
I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.Problem is, without any relational integrity and constraints designed into the database, it probably already contains a lot of data that violates the logical relationships. That makes it impossible for any tool to definitively say what the relationships should be based solely upon the existing data.
I have a script that finds natural keys within a table, which you can use to set the primary key, but that's about it.
Chances are, 10% of your time is going to be occupied with finding out what the relationships are supposed to be, while 90% will involve fixing the bad data you find.
And this: "Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables" is total bull. They are just bad developers. I can set a constraint or a foreign key in 30 seconds. They just didn't want to be bothered taking the time to make sure their code submitted correct data to the database, and so they allowed the database to accept any old crap that is sent to it. That's why you have a mess on your hands.|||I used ERWin in order to deduce references. But unfortunately even that didn't suggest much. ERWin tries to deduce what would be relationships between tables. I guess now I have to use logic in order to figure out what the relationships would be.

Discover exactly what is causing a block

Hi. We are experiencing significant prolonged blocking when loading multiple
files in through a partitioned view. Before we start working on a resolution
we want to identify exactly what resource is causing the block.
We can see which processes are being blocked by using sp_who2. But how do we
determine exactly which lock on which object is causing the blocking?
Thanks!
McGy
[url]http://mcgy.blogspot.com[/url]sp_lock2 (it's out there on the internet) will tell you by spid which
database resources are being locked up.
This will be a good starting point.|||Also take a look at aba_lockinfo written by SQL Server MVP Erland
Sommarskog
Source code is available from the link below
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
Denis the SQL Menace
http://sqlservercode.blogspot.com/
McGy wrote:
> Hi. We are experiencing significant prolonged blocking when loading multip
le
> files in through a partitioned view. Before we start working on a resoluti
on
> we want to identify exactly what resource is causing the block.
> We can see which processes are being blocked by using sp_who2. But how do
we
> determine exactly which lock on which object is causing the blocking?
> Thanks!
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]|||> We can see which processes are being blocked by using sp_who2. But how do
> we determine exactly which lock on which object is causing the blocking?
1) Execute sp_lock for the blocked spid. For example:
EXEC sp_lock 51
2) Identify the row with status WAIT and note the ObjId and indid
3) Determine the object/index
USE MyDatabase
SELECT
OBJECT_NAME(id) AS TableName,
name AS IndexName
FROM sysindexes
WHERE
id = 10000 --ObjId from sp_lock
AND indid = 1 --indid from sp_lock
Hope this helps.
Dan Guzman
SQL Server MVP
"McGy" <anon@.anon.com> wrote in message
news:eoPC9chiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> Hi. We are experiencing significant prolonged blocking when loading
> multiple files in through a partitioned view. Before we start working on a
> resolution we want to identify exactly what resource is causing the block.
> We can see which processes are being blocked by using sp_who2. But how do
> we determine exactly which lock on which object is causing the blocking?
> Thanks!
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
>|||Thank you very much for all of your tips. They have been invaluable!!
McGy
[url]http://mcgy.blogspot.com[/url]
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OUyKp8iiGHA.4056@.TK2MSFTNGP02.phx.gbl...
> 1) Execute sp_lock for the blocked spid. For example:
> EXEC sp_lock 51
> 2) Identify the row with status WAIT and note the ObjId and indid
> 3) Determine the object/index
> USE MyDatabase
> SELECT
> OBJECT_NAME(id) AS TableName,
> name AS IndexName
> FROM sysindexes
> WHERE
> id = 10000 --ObjId from sp_lock
> AND indid = 1 --indid from sp_lock
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "McGy" <anon@.anon.com> wrote in message
> news:eoPC9chiGHA.4512@.TK2MSFTNGP02.phx.gbl...
>

discover all permissions for a user

Hi folks,

Is there any easy way of finding out, using a query, all permissions that a user has on any securable? Also, what sort of permission (e.g. ALTER, SELECT, INSERT etc..)

I'm going to hunt around on Google but thought I'd post here in case anyone can tell me before I find it.

thanks in advance.

-Jamie

ignore this.

select *

from sys.database_permissions

does me right!

Doh!

-Jamie