FIND PK across database?

Hi,
I wanted to know list of tables which are not having Primary key across
the database.How to get that list?
does any on ehave any script which tell us which are those tables don't
have primary keys across the database?
thx
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi,
Attached the procedure to identify the tables which do not have primary key
across databases. This procedure takes database name as parameter.
Craete proc pkey @.dbname varchar(30)
as
begin
set quoted_identifier off
declare @.sql varchar(1000)
EXEC ('use '+@.dbname+ ' select name as Table_name from sysobjects where name
not in(select object_name(parent_obj) from sysobjects where type=''K'') and
type =''u''')
end
How to execute
--
Exec pkey 'master'
Thanks
Hari
MCDBA
"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
> Hi,
> I wanted to know list of tables which are not having Primary key across
> the database.How to get that list?
> does any on ehave any script which tell us which are those tables don't
> have primary keys across the database?
> thx
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||not sure what you mean by 'across the database'.
anyway, here is a query that will return the names of the tables without pri
mary
key based on the defined database.
e.g.
declare @.db sysname
set @.db='Northwind'
exec('select name
from '+@.db+'..sysobjects o1
where xtype=''U''
and not exists(select *
from '+@.db+'..sysobjects
where xtype=''PK''
and parent_obj=o1.id)')
-oj
http://www.rac4sql.net
"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
> Hi,
> I wanted to know list of tables which are not having Primary key across
> the database.How to get that list?
> does any on ehave any script which tell us which are those tables don't
> have primary keys across the database?
> thx
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Hi
I have a DB with 16 tables which doesnt have a PK (this db
is designed by someone else). What are the outcomes in
terms of query response etc and DB integrity , when a
client application uses these tables. Also pls let me know
does primary key plays any major role in data **retrival**
from a client application. Also when do I need not go for
a primary key for a Table?
Sorry if my query is foolish. I am learning things about
DB Design and I want to make sure that I am learning good
things.
Sincerely
Chip
>--Original Message--
>not sure what you mean by 'across the database'.
>anyway, here is a query that will return the names of the
tables without primary
>key based on the defined database.
>e.g.
>declare @.db sysname
>set @.db='Northwind'
>exec('select name
>from '+@.db+'..sysobjects o1
>where xtype=''U''
>and not exists(select *
>from '+@.db+'..sysobjects
>where xtype=''PK''
>and parent_obj=o1.id)')
>
>--
>-oj
>http://www.rac4sql.net
>
>"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
>news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
Primary key across
those tables don't
***
>
>.
>

FIND PK across database?

Hi,
I wanted to know list of tables which are not having Primary key across
the database.How to get that list?
does any on ehave any script which tell us which are those tables don't
have primary keys across the database?
thx
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi,
Attached the procedure to identify the tables which do not have primary key
across databases. This procedure takes database name as parameter.
Craete proc pkey @.dbname varchar(30)
as
begin
set quoted_identifier off
declare @.sql varchar(1000)
EXEC ('use '+@.dbname+ ' select name as Table_name from sysobjects where name
not in(select object_name(parent_obj) from sysobjects where type=''K'') and
type =''u''')
end
How to execute
--
Exec pkey 'master'
Thanks
Hari
MCDBA
"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
> Hi,
> I wanted to know list of tables which are not having Primary key across
> the database.How to get that list?
> does any on ehave any script which tell us which are those tables don't
> have primary keys across the database?
> thx
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||not sure what you mean by 'across the database'.
anyway, here is a query that will return the names of the tables without primary
key based on the defined database.
e.g.
declare @.db sysname
set @.db='Northwind'
exec('select name
from '+@.db+'..sysobjects o1
where xtype=''U''
and not exists(select *
from '+@.db+'..sysobjects
where xtype=''PK''
and parent_obj=o1.id)')
-oj
http://www.rac4sql.net
"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
> Hi,
> I wanted to know list of tables which are not having Primary key across
> the database.How to get that list?
> does any on ehave any script which tell us which are those tables don't
> have primary keys across the database?
> thx
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi
I have a DB with 16 tables which doesnt have a PK (this db
is designed by someone else). What are the outcomes in
terms of query response etc and DB integrity , when a
client application uses these tables. Also pls let me know
does primary key plays any major role in data **retrival**
from a client application. Also when do I need not go for
a primary key for a Table?
Sorry if my query is foolish. I am learning things about
DB Design and I want to make sure that I am learning good
things.
Sincerely
Chip
>--Original Message--
>not sure what you mean by 'across the database'.
>anyway, here is a query that will return the names of the
tables without primary
>key based on the defined database.
>e.g.
>declare @.db sysname
>set @.db='Northwind'
>exec('select name
>from '+@.db+'..sysobjects o1
>where xtype=''U''
>and not exists(select *
>from '+@.db+'..sysobjects
>where xtype=''PK''
>and parent_obj=o1.id)')
>
>--
>-oj
>http://www.rac4sql.net
>
>"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
>news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
>> Hi,
>> I wanted to know list of tables which are not having
Primary key across
>> the database.How to get that list?
>> does any on ehave any script which tell us which are
those tables don't
>> have primary keys across the database?
>> thx
>>
>> *** Sent via Developersdex http://www.developersdex.com
***
>> Don't just participate in USENET...get rewarded for it!
>
>.
>

Find In...Stored Procedures?

All:
Is there a way to do a "Find" across the text of a set of stored
procedures? (SQL Server 2000)
I am in the process of doing some code refactoring and have eliminated
a column in one of my tables. Now, I want to find all the stored
procedures that use the column name.
Is there a way to do this? Alternatives?
Many thanks,
JohnHow do I find a stored procedure containing <text>?
http://www.aspfaq.com/show.asp?id=2037
AMB
"jpuopolo@.mvisiontechnology.com" wrote:

> All:
> Is there a way to do a "Find" across the text of a set of stored
> procedures? (SQL Server 2000)
> I am in the process of doing some code refactoring and have eliminated
> a column in one of my tables. Now, I want to find all the stored
> procedures that use the column name.
> Is there a way to do this? Alternatives?
> Many thanks,
> John
>|||Look at the syscomments table; it contains the text for your stored
procedures and views.
SLECT *
FROM syscomments
WHERE text like '%[column name]%'
HTH,
Stu|||Stu:
Thanks - worked like a charm.
Best,
John|||Use dbname
go
SELECT OBJECT_NAME(ID) from syscomments
where TEXT like '%columnname%'
Thanks
Hari
SQL Server MVP
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126359684.486000.4260@.g49g2000cwa.googlegroups.com...
> Look at the syscomments table; it contains the text for your stored
> procedures and views.
> SLECT *
> FROM syscomments
> WHERE text like '%[column name]%'
> HTH,
> Stu
>

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...
>
>

Filtering structure across 2 dimensions

I would appreciate any help with the following problem:

Other than cube redesign, which I know we can accomplish, if theres an actual way through MDX to filter structure between 2 dimensions. As of right now I can only accomplish filtering measures by 2 dimensions. Example:

SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )

What occurs is all members from Dimension1 is returned and the Measures are filtered accordingly. What I want is it to only bring back the rows which have a relationship with Dimension2. Is this possible?

But if I try to filter structure across 2 different heirarchies in the same dimension it works fine.

SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[Heirarchy2].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension1].[Heirarchy1].[AnyHeirarchyLevel].[AnyMember] )

This only returns rows in which the two heirarchy members are related.

So obvisouly If I moved my 2nd Dimension under the 1st Dimesion and make it a heirachy it would work, but any other way around this?

Thanks a lot.

When you say: "only bring back the rows which have a relationship with Dimension2", are they only related via the fact table, or in some other way? In the former case, try Non Empty, like:

SELECT { [Measures].Members } ON COLUMNS,
Non Empty [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )

|||

Thanks for the reply.

Yes, they are only related via the fact table. If I try non empty, and please correct me if im wrong, that will filter by measures (sales, units, etc), and only show members that have sales/units. In essence exluding the real relationships (members that have no sales won't be shown)

I would like to bring back every member that is related to Dimension 2's member, regardless of no sales or not.

Thanks.

|||

The fact of sales is a relation between dimension1 and dimension2. There isn't another relation between dimension1 and dimension2.

If you have another one the it should exists as another fact table or anything else.