Finding long-running blocks

Can anyone point me to a script (or alternative method) which will find bloc
king that has been going on for a while?
TIA,
John BrownJohn
http://support.microsoft.com/defaul...224453-- :
Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
(Q224453)
"John Brown" <anonymous@.discussions.microsoft.com> wrote in message
news:9F0A15E4-4DBB-4F4F-8DDE-1E822E0300E3@.microsoft.com...
> Can anyone point me to a script (or alternative method) which will find
blocking that has been going on for a while?
> TIA,
> John Brown|||Hi.
You can utilize the SQL Profiler [SQL Enterprise Manager -> Tools -> SQL
Profiler] or The Windows 2000 Monitor to verify the blocks of your database
. Through the Monitor can see a histograma graphic.
Hermilson.
****************************************
******************************
Can anyone point me to a script (or alternative method) which will find bloc
king that has been going on for a while?|||Spot on - thanks!
John

Finding long-running blocks

Can anyone point me to a script (or alternative method) which will find blocking that has been going on for a while
TIA
John BrownJohn
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224453-- :
Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
(Q224453)
"John Brown" <anonymous@.discussions.microsoft.com> wrote in message
news:9F0A15E4-4DBB-4F4F-8DDE-1E822E0300E3@.microsoft.com...
> Can anyone point me to a script (or alternative method) which will find
blocking that has been going on for a while?
> TIA,
> John Brown|||Hi
You can utilize the SQL Profiler [SQL Enterprise Manager -> Tools -> SQL Profiler] or The Windows 2000 Monitor to verify the blocks of your database. Through the Monitor can see a histograma graphic
Hermilson
*********************************************************************
Can anyone point me to a script (or alternative method) which will find blocking that has been going on for a while|||Spot on - thanks
Johnsql

Finding Indexes

Hi,
Does anyone have a script for or know the easiest way to list the indexes in
all tables (for all user databases) in sql server 2000?
Thanks,
AndrewThat's easy. The easiest way to list all indexes for all tables in a DB is:
select * from MyDB.dbo.sysindexes
Of course, it's not very user friendly - you might want to run it from
the actual DB and throw in some functions like object_name([id]). For
example,
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
order by object_name([id]), indid
For more info see the BOL entries on the sysindexes system table. For
instance, indid 0 for a table is the data (ie. there is no clustered
index for that table - it's a heap), index 1 is the clustered index, 2
to 254 are nonclustered indexes, 255 is for text/image data, etc.
Anyway, check out BOL.
Cheers,
Mike.
Andrew wrote:
> Hi,
> Does anyone have a script for or know the easiest way to list the indexes in
> all tables (for all user databases) in sql server 2000?
> Thanks,
> Andrew
>|||Also, thinks like statistics and hypothetical indexes need to be filtered out. Check out the
INDEXPROPERTY function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:%23zIgFhy5EHA.3648@.TK2MSFTNGP11.phx.gbl...
> That's easy. The easiest way to list all indexes for all tables in a DB is:
> select * from MyDB.dbo.sysindexes
> Of course, it's not very user friendly - you might want to run it from the actual DB and throw in
> some functions like object_name([id]). For example,
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> order by object_name([id]), indid
> For more info see the BOL entries on the sysindexes system table. For instance, indid 0 for a
> table is the data (ie. there is no clustered index for that table - it's a heap), index 1 is the
> clustered index, 2 to 254 are nonclustered indexes, 255 is for text/image data, etc. Anyway, check
> out BOL.
> Cheers,
> Mike.
> Andrew wrote:
>> Hi,
>> Does anyone have a script for or know the easiest way to list the indexes in
>> all tables (for all user databases) in sql server 2000?
>> Thanks,
>> Andrew|||OK then, how about (not tested):
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
order by object_name([id]), indid
If you want it for all user DBs you can wrap it in a cursor like:
declare @.cmd nvarchar(4000)
declare @.DBName sysname
declare AllUserDBs cursor for
select [name] from master.dbo.sysdatabases
where [name] not in ('master', 'msdb', 'model', 'tempdb',
'distribution', 'pubs', 'Northwind')
order by [name]
open AllUserDBs
fetch next from AllUserDBs into @.DBName
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd = 'use [@.DB]; ' +
' select @.DB as [DB], object_name([id]), indid, [name] from
dbo.sysindexes ' +
' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
' order by object_name([id]), indid'
exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
end
fetch next from AllUserDBs into @.DBName
end
close AllUserDBs
deallocate AllUserDBs
(Please pardon any typos - this is untested and off the top of my head.)
Cheers,
Mike.
Tibor Karaszi wrote:
> Also, thinks like statistics and hypothetical indexes need to be
> filtered out. Check out the INDEXPROPERTY function.
>|||Sorry, I found a typo - the WHERE clause should be:
where INDEXPROPERTY([id], [name], 'IsStatistics') = 0
Mike Hodgson wrote:
> OK then, how about (not tested):
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
> order by object_name([id]), indid
> If you want it for all user DBs you can wrap it in a cursor like:
> declare @.cmd nvarchar(4000)
> declare @.DBName sysname
> declare AllUserDBs cursor for
> select [name] from master.dbo.sysdatabases
> where [name] not in ('master', 'msdb', 'model', 'tempdb',
> 'distribution', 'pubs', 'Northwind')
> order by [name]
> open AllUserDBs
> fetch next from AllUserDBs into @.DBName
> while (@.@.FETCH_STATUS != -1)
> begin
> if (@.@.FETCH_STATUS != -2)
> begin
> select @.cmd => 'use [@.DB]; ' +
> ' select @.DB as [DB], object_name([id]), indid, [name]
> from dbo.sysindexes ' +
> ' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
> ' order by object_name([id]), indid'
> exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
> end
> fetch next from AllUserDBs into @.DBName
> end
> close AllUserDBs
> deallocate AllUserDBs
> (Please pardon any typos - this is untested and off the top of my head.)
> Cheers,
> Mike.
> Tibor Karaszi wrote:
>> Also, thinks like statistics and hypothetical indexes need to be
>> filtered out. Check out the INDEXPROPERTY function.

Finding Indexes

Hi,
Does anyone have a script for or know the easiest way to list the indexes in
all tables (for all user databases) in sql server 2000?
Thanks,
Andrew
That's easy. The easiest way to list all indexes for all tables in a DB is:
select * from MyDB.dbo.sysindexes
Of course, it's not very user friendly - you might want to run it from
the actual DB and throw in some functions like object_name([id]). For
example,
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
order by object_name([id]), indid
For more info see the BOL entries on the sysindexes system table. For
instance, indid 0 for a table is the data (ie. there is no clustered
index for that table - it's a heap), index 1 is the clustered index, 2
to 254 are nonclustered indexes, 255 is for text/image data, etc.
Anyway, check out BOL.
Cheers,
Mike.
Andrew wrote:
> Hi,
> Does anyone have a script for or know the easiest way to list the indexes in
> all tables (for all user databases) in sql server 2000?
> Thanks,
> Andrew
>
|||Also, thinks like statistics and hypothetical indexes need to be filtered out. Check out the
INDEXPROPERTY function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:%23zIgFhy5EHA.3648@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> That's easy. The easiest way to list all indexes for all tables in a DB is:
> select * from MyDB.dbo.sysindexes
> Of course, it's not very user friendly - you might want to run it from the actual DB and throw in
> some functions like object_name([id]). For example,
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> order by object_name([id]), indid
> For more info see the BOL entries on the sysindexes system table. For instance, indid 0 for a
> table is the data (ie. there is no clustered index for that table - it's a heap), index 1 is the
> clustered index, 2 to 254 are nonclustered indexes, 255 is for text/image data, etc. Anyway, check
> out BOL.
> Cheers,
> Mike.
> Andrew wrote:
|||OK then, how about (not tested):
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
order by object_name([id]), indid
If you want it for all user DBs you can wrap it in a cursor like:
declare @.cmd nvarchar(4000)
declare @.DBName sysname
declare AllUserDBs cursor for
select [name] from master.dbo.sysdatabases
where [name] not in ('master', 'msdb', 'model', 'tempdb',
'distribution', 'pubs', 'Northwind')
order by [name]
open AllUserDBs
fetch next from AllUserDBs into @.DBName
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd =
'use [@.DB]; ' +
'select @.DB as [DB], object_name([id]), indid, [name] from
dbo.sysindexes ' +
'where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
'order by object_name([id]), indid'
exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
end
fetch next from AllUserDBs into @.DBName
end
close AllUserDBs
deallocate AllUserDBs
(Please pardon any typos - this is untested and off the top of my head.)
Cheers,
Mike.
Tibor Karaszi wrote:
> Also, thinks like statistics and hypothetical indexes need to be
> filtered out. Check out the INDEXPROPERTY function.
>
|||Sorry, I found a typo - the WHERE clause should be:
where INDEXPROPERTY([id], [name], 'IsStatistics') = 0
Mike Hodgson wrote:[vbcol=seagreen]
> OK then, how about (not tested):
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
> order by object_name([id]), indid
> If you want it for all user DBs you can wrap it in a cursor like:
> declare @.cmd nvarchar(4000)
> declare @.DBName sysname
> declare AllUserDBs cursor for
> select [name] from master.dbo.sysdatabases
> where [name] not in ('master', 'msdb', 'model', 'tempdb',
> 'distribution', 'pubs', 'Northwind')
> order by [name]
> open AllUserDBs
> fetch next from AllUserDBs into @.DBName
> while (@.@.FETCH_STATUS != -1)
> begin
> if (@.@.FETCH_STATUS != -2)
> begin
> select @.cmd =
> 'use [@.DB]; ' +
> ' select @.DB as [DB], object_name([id]), indid, [name]
> from dbo.sysindexes ' +
> ' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
> ' order by object_name([id]), indid'
> exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
> end
> fetch next from AllUserDBs into @.DBName
> end
> close AllUserDBs
> deallocate AllUserDBs
> (Please pardon any typos - this is untested and off the top of my head.)
> Cheers,
> Mike.
> Tibor Karaszi wrote:

Finding Containers and Variables from Script task?

Hi,
* Here's my use case:
I have 20 odd event handlers in various containers that all do the exact same thing. Even though they're simple (a Script and an Execute SQL Task), its a right pain to copy them & keep them synced. I could probably create a custom task for the content of each handler, but I would still need to copy & configure it across 20 event handlers.
My (maybe impossible) idea to handle this was to:
Create a single event handler at the global scope. When invoked, use System::SourceID or System::SourceName to either
- GetContainer(SourceID).Variables("myVar") (had there been such a function call)
- Traverse the package object model to find the container, i.e:
Package.Executables(SourceName).Variables("myVar")
Is it true that there is no practical way of doing this?

* "Variables on the container ... are visible to the event handler that handles the event on that container"
As I understand it, this is only true when attaching an event handler to the specific container generating the event. If a container has no event handler, the event will propagate up through the hierarchy all the way to the package scope if needed. As soon as the event has propagated even once, the invoked event handler no longer has access to the local variables of the source container. Is this a reasonably correct description?
Event handlers and propagation would be _strikingly_ more useful if the handler had access to the source container environment that created the event, or am I missing something obvious?

* As a second possibility, can I create a _custom task_ (as opposed to a script) that _can_ traverse the object model of a package created in the designer (i.e. I'm not creating the whole package in my own code)?

If you lasted this long, thanks!-)
KI think I answered this on the NG, but the variables on the parent of the eventhandler that raised the event are visible to the event handler.
You can also create a package that handle the events and uses parent package configurations to pass in the values for the events. Then use an execute package task in all your event handlers that references that shared package.
Tasks cannot traverse the object model. They are prohibited and variables will refuse to hold a reference to IS object model objects with an error.|||Although I did implement using those 20 event handlers, I've now ditched that in favour of parsing the needed info out of sysdtslog90, which does away with the need for all those event handlers. Always a good feeling when stripping out half the 'code' but none of the functionality
Very useful to understand how & when to use the event handlers though, thanks!
K

Finding a creator of object

Hi all,

whenever dbo is prifixed with the create script the owner becomes dbo.
If the below script
run by the sam the owner becomes dbo

create proc dbo.test
as
print 'hello'

Is there any place where SQL server keeps the record of creator?shiju (shiju.samuel@.gmail.com) writes:

Quote:

Originally Posted by

whenever dbo is prifixed with the create script the owner becomes dbo.
If the below script
run by the sam the owner becomes dbo
>
create proc dbo.test
as
print 'hello'
>
Is there any place where SQL server keeps the record of creator?

No. You would have to have trace running that captutes the Object:Created
event. In SQL 2005 you could also set up a DLL trigger.

--
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 start and end date strings

Hello,

I am having some probelms getting script to give me the first and last date a customer had an outstanding item. The data is as follows:

Customer StartDate EndDate
A 4/1/04 4/15/04
A 4/15/04 5/1/04
A 5/1/04 5/15/04
A 5/16/04 5/28/04
A 5/28/04 6/5/04
B 5/1/04 5/15/04
B 5/16/04 5/20/04

The results I am looking for would be as follows:

Customer A : Outstanding 4/1/04 - 5/15/04
Customer A : Outstanding 5/16/04 - 5/28/04 (Theres a one day gap between prior sting, so this would be a new string )
Customer B : OUtstanding 5/1/04 - 5/15/04
Customer B : Outstanding 5/16/04 - 5/20/04

I want to include any strings where the start of one item and the end of another are on the same day as one continuis string. Any ideas on how to do this??

Thanks in advance!!bump..any one have ideas??|||How about something like this?

Declare @.sCustomer as varchar(5)
Declare @.dtStartDate as datetime
Declare @.dtEndDate as Datetime
Declare @.sSaveCustomer as varchar(5)
Declare @.dtSaveStartDate as DateTime
Declare @.dtLinkDate as DateTime

DECLARE Test CURSOR FOR
SELECT * FROM Test ORDER BY Customer, StartDate, EndDate

CREATE table #tmp (Customer varchar(5), StartDate DateTime, EndDate DateTime)

OPEN TEST

Fetch next from Test into @.sCustomer, @.dtStartDate, @.dtEndDate

SET @.sSaveCustomer = @.sCustomer
SET @.dtSaveStartDate = @.dtStartDate
SET @.dtLinkDate = @.dtEndDate

if (@.@.FETCH_STATUS = 0)
BEGIN
Fetch next from Test into @.sCustomer, @.dtStartDate, @.dtEndDate
END

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
if ((@.sSaveCustomer = @.sCustomer) AND (@.dtStartDate = @.dtLinkDate))
BEGIN
SET @.dtLinkDate = @.dtEndDate
END
ELSE
BEGIN
INSERT INTO #tmp VALUES(@.sSaveCustomer, @.dtSaveStartDate, @.dtLinkDate)
SET @.sSaveCustomer = @.sCustomer
SET @.dtSaveStartDate = @.dtStartDate
SET @.dtLinkDate = @.dtEndDate
END

Fetch next from Test into @.sCustomer, @.dtStartDate, @.dtEndDate
END

INSERT INTO #tmp VALUES(@.sSaveCustomer, @.dtSaveStartDate, @.dtLinkDate)

select * from #tmp

Drop table #tmp

Close Test
Deallocate Test

Have some fun.|||A similar question was asked in this thread.

http://www.dbforums.com/t1005647.html|||Thanks!! Both options worked!!

Find SQL Servers

Hi,
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.Please take a look here:
http://sqlserver2000.databases.aspf...my-network.html
Cheers,
Paul Ibison|||Thanks a lot
"Paul Ibison" wrote:

> Please take a look here:
> http://sqlserver2000.databases.aspf...my-network.html
> Cheers,
> Paul Ibison
>
>

Find SQL Servers

Hi,
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.
Thanks a lot
"Paul Ibison" wrote:

> Please take a look here:
> http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
> Cheers,
> Paul Ibison
>
>

Find SQL Servers

Hi,
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.Please take a look here:
http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
Cheers,
Paul Ibison|||Thanks a lot
"Paul Ibison" wrote:
> Please take a look here:
> http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
> Cheers,
> Paul Ibison
>
>

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 locks in a database by using sp_lock

I’m trying to find locks in a database by using sp_lock. However the sp_h
elp
give me an object name as a number. Does anyone have any script which
provide the tables name itself.
Best regards,
Ron
sp_lock to get the object number and then to translate the number to table
name.
SELECT TABLE_CATALOG, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = OBJECT_NAME(85575343)See if this helps: http://vyaskn.tripod.com/code.htm#sp_lock2
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
news:26AFF5F6-E19A-4D6D-AD8D-D61774DC9B13@.microsoft.com...
> I'm trying to find locks in a database by using sp_lock. However the
sp_help
> give me an object name as a number. Does anyone have any script which
> provide the tables name itself.
> Best regards,
> Ron
> sp_lock to get the object number and then to translate the number to table
> name.
> SELECT TABLE_CATALOG, TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = OBJECT_NAME(85575343)
>|||If you use the below script, make sure you follow Vyas's directions on his
webpage -- i.e. run it in the database whose locks you are interested in.
If you run it from a different database the script is likely to display the
wrong object names. This is because the object_name function operates in
the context of the current database while sp_lock displays locks from all
databases. Thus if you have locks on tables DB1.dbo.Foo and DB2.dbo.Bar and
lets say that these two tables happen to have the same object id in their
respective databases, then if you run sp_lock2 from DB1 it will display Foo
for both locks; if you run it from DB2 it will display Bar for both locks;
and if you run it from master it will display the name of whatever table in
master has the corresponding object id, or null if master does not have a
table with that id.
Santeri (Santtu) Voutilainen
This posting is provided "AS IS" with no warranties, and confers no rights.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uNl$cVyXFHA.3188@.TK2MSFTNGP09.phx.gbl...
> See if this helps: http://vyaskn.tripod.com/code.htm#sp_lock2
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:26AFF5F6-E19A-4D6D-AD8D-D61774DC9B13@.microsoft.com...
> sp_help
>

find indexes

I'm a new user of SQL Server.
What's the script to find out all indexes for tables and
views?
Thanks.
Hi,
sp_helpindex <table name or view name>
You can also use the below statement to get all the information of the
object (including index).
sp_help <table or view name>
Thanks
Hari
MCDBA
"Julia" <KQD02@.YAHOO.COM> wrote in message
news:143d001c444cd$ab9aa640$a601280a@.phx.gbl...
> I'm a new user of SQL Server.
> What's the script to find out all indexes for tables and
> views?
> Thanks.
|||Thanks, is it possible to get all indexes for all tables
and views in one script?

>--Original Message--
>Hi,
>sp_helpindex <table name or view name>
>You can also use the below statement to get all the
information of the
>object (including index).
>sp_help <table or view name>
>Thanks
>Hari
>MCDBA
>
>"Julia" <KQD02@.YAHOO.COM> wrote in message
>news:143d001c444cd$ab9aa640$a601280a@.phx.gbl...
>
>.
>
|||Hi,
Execute the below script in query analyzer
Use dbname
go
select substring(a.name,1,20) as Table_View_name,substring(b.name,1,20) as
Index_name,type as Object_type,indid
from sysobjects a, sysindexes b
where a.id=b.id
and type in ('U','V')
and indid between 1 and 254
-- If indid = 1 then Clustered index
-- indid >1 then Nonclustered index
Thanks
Hari
MCDBA
Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:1443901c444d2$9074c850$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks, is it possible to get all indexes for all tables
> and views in one script?
>
> information of the

find indexes

I'm a new user of SQL Server.
What's the script to find out all indexes for tables and
views?
Thanks.Hi,
sp_helpindex <table name or view name>
You can also use the below statement to get all the information of the
object (including index).
sp_help <table or view name>
Thanks
Hari
MCDBA
"Julia" <KQD02@.YAHOO.COM> wrote in message
news:143d001c444cd$ab9aa640$a601280a@.phx.gbl...
> I'm a new user of SQL Server.
> What's the script to find out all indexes for tables and
> views?
> Thanks.|||Thanks, is it possible to get all indexes for all tables
and views in one script?
>--Original Message--
>Hi,
>sp_helpindex <table name or view name>
>You can also use the below statement to get all the
information of the
>object (including index).
>sp_help <table or view name>
>Thanks
>Hari
>MCDBA
>
>"Julia" <KQD02@.YAHOO.COM> wrote in message
>news:143d001c444cd$ab9aa640$a601280a@.phx.gbl...
>> I'm a new user of SQL Server.
>> What's the script to find out all indexes for tables and
>> views?
>> Thanks.
>
>.
>|||Hi,
Execute the below script in query analyzer
Use dbname
go
select substring(a.name,1,20) as Table_View_name,substring(b.name,1,20) as
Index_name,type as Object_type,indid
from sysobjects a, sysindexes b
where a.id=b.id
and type in ('U','V')
and indid between 1 and 254
-- If indid = 1 then Clustered index
-- indid >1 then Nonclustered index
Thanks
Hari
MCDBA
Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:1443901c444d2$9074c850$a301280a@.phx.gbl...
> Thanks, is it possible to get all indexes for all tables
> and views in one script?
>
> >--Original Message--
> >Hi,
> >
> >sp_helpindex <table name or view name>
> >
> >You can also use the below statement to get all the
> information of the
> >object (including index).
> >
> >sp_help <table or view name>
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >"Julia" <KQD02@.YAHOO.COM> wrote in message
> >news:143d001c444cd$ab9aa640$a601280a@.phx.gbl...
> >> I'm a new user of SQL Server.
> >> What's the script to find out all indexes for tables and
> >> views?
> >> Thanks.
> >
> >
> >.
> >

find indexes

I'm a new user of SQL Server.
What's the script to find out all indexes for tables and
views?
Thanks.Hi,
sp_helpindex <table name or view name>
You can also use the below statement to get all the information of the
object (including index).
sp_help <table or view name>
Thanks
Hari
MCDBA
"Julia" <KQD02@.YAHOO.COM> wrote in message
news:143d001c444cd$ab9aa640$a601280a@.phx
.gbl...
> I'm a new user of SQL Server.
> What's the script to find out all indexes for tables and
> views?
> Thanks.|||Thanks, is it possible to get all indexes for all tables
and views in one script?

>--Original Message--
>Hi,
>sp_helpindex <table name or view name>
>You can also use the below statement to get all the
information of the
>object (including index).
>sp_help <table or view name>
>Thanks
>Hari
>MCDBA
>
>"Julia" <KQD02@.YAHOO.COM> wrote in message
> news:143d001c444cd$ab9aa640$a601280a@.phx
.gbl...
>
>.
>

find index for all tables and views in one script

Is it possible to get all indexes for all tables
and views in one script?Please don't multi-post; you have a thread going on elsewhere.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:146c101c444dc$362959b0$a001280a@.phx
.gbl...
> Is it possible to get all indexes for all tables
> and views in one script?
>|||Yup, you need to query sysobjects and sysindexes system tables.
Eric
Julia wrote:

> Is it possible to get all indexes for all tables
> and views in one script?
>
Eric Li
SQL DBA
MCDBA

find index for all tables and views in one script

Is it possible to get all indexes for all tables
and views in one script?Please don't multi-post; you have a thread going on elsewhere.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:146c101c444dc$362959b0$a001280a@.phx.gbl...
> Is it possible to get all indexes for all tables
> and views in one script?
>|||Yup, you need to query sysobjects and sysindexes system tables.
Eric
Julia wrote:
> Is it possible to get all indexes for all tables
> and views in one script?
>
Eric Li
SQL DBA
MCDBA|||Please look my reply in your old post.
Thanks
Hari
MCDBA
"Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:146c101c444dc$362959b0$a001280a@.phx.gbl...
> Is it possible to get all indexes for all tables
> and views in one script?
>

find index for all tables and views in one script

Is it possible to get all indexes for all tables
and views in one script?
Please don't multi-post; you have a thread going on elsewhere.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:146c101c444dc$362959b0$a001280a@.phx.gbl...
> Is it possible to get all indexes for all tables
> and views in one script?
>
|||Yup, you need to query sysobjects and sysindexes system tables.
Eric
Julia wrote:

> Is it possible to get all indexes for all tables
> and views in one script?
>
Eric Li
SQL DBA
MCDBA
|||Please look my reply in your old post.
Thanks
Hari
MCDBA
"Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:146c101c444dc$362959b0$a001280a@.phx.gbl...
> Is it possible to get all indexes for all tables
> and views in one script?
>

find datetime fields - swap year portion of datetime

I need to devise a t-sql script to:
1.) loop through all user tables in my db,
2.) determine whether each table has one or more datetime fields, and
if it does,
3.) swap the year portion of each rows datetime with a different year
(e.g., '1-Jan-1996' would become '1-Jan-2018')
I know how to do '1', have an idea how to do '3' using the DatePart
and CAST functions, but haven't had much luck figuring out '2', and
*then iterating row by row through the table's datetime rows to swap
in the new years. To further automate this, I envisioned adding a
table to the db that defined the mapping for years, i.e.,...
tblBlindedYears
YearNow |BecomesYear
1996 2018
1997 2008
(With this, I would look to eliminate leap year issues by making sure
I don't swap a non-leap year with a leap-year).
Any help GREATLY appreciated.To help you with the issue 2):
USE Northwind
GO
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE, COUNT(*) As 'NofCols'
FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE DATA_TYPE IN ('datetime','smaldatetime')
AND t.TABLE_TYPE='BASE TABLE'
GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
news:ed564348.0401021712.247c36da@.posting.google.com...
> I need to devise a t-sql script to:
> 1.) loop through all user tables in my db,
> 2.) determine whether each table has one or more datetime fields, and
> if it does,
> 3.) swap the year portion of each rows datetime with a different year
> (e.g., '1-Jan-1996' would become '1-Jan-2018')
> I know how to do '1', have an idea how to do '3' using the DatePart
> and CAST functions, but haven't had much luck figuring out '2', and
> *then iterating row by row through the table's datetime rows to swap
> in the new years. To further automate this, I envisioned adding a
> table to the db that defined the mapping for years, i.e.,...
> tblBlindedYears
> YearNow |BecomesYear
> 1996 2018
> 1997 2008
> (With this, I would look to eliminate leap year issues by making sure
> I don't swap a non-leap year with a leap-year).
> Any help GREATLY appreciated.|||This is very helpful, thank-you.
Now that I have this list of table names having counts of datetime
columns, any suggestions as to discovering *which* columns have the
datetime, and how I can begin printing these particular columns
values?
p.s. my results were:
dbo tbl10DayMeanAirTemp datetime 1
dbo tblHistoricalStationFlows datetime 1
dbo tblHistoricalStationWaterElevation datetime 1
dbo tblHistoricalStationWaterTemperatures datetime 1
dbo tblHydrologyModelWeeks datetime 2
dbo tblModelRuns datetime 5
dbo tblModelScenarios datetime 2
dbo tblXLFileLocations datetime 1
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<Ok3w2rd0DHA.1532@.TK2MSFTNGP10.phx.gbl>...
> To help you with the issue 2):
> USE Northwind
> GO
> SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE, COUNT(*) As 'NofCols'
> FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t
> ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
> WHERE DATA_TYPE IN ('datetime','smaldatetime')
> AND t.TABLE_TYPE='BASE TABLE'
> GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
> ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
> news:ed564348.0401021712.247c36da@.posting.google.com...
> > I need to devise a t-sql script to:
> >
> > 1.) loop through all user tables in my db,
> > 2.) determine whether each table has one or more datetime fields, and
> > if it does,
> > 3.) swap the year portion of each rows datetime with a different year
> > (e.g., '1-Jan-1996' would become '1-Jan-2018')
> >
> > I know how to do '1', have an idea how to do '3' using the DatePart
> > and CAST functions, but haven't had much luck figuring out '2', and
> > *then iterating row by row through the table's datetime rows to swap
> > in the new years. To further automate this, I envisioned adding a
> > table to the db that defined the mapping for years, i.e.,...
> >
> > tblBlindedYears
> > YearNow |BecomesYear
> > 1996 2018
> > 1997 2008
> >
> > (With this, I would look to eliminate leap year issues by making sure
> > I don't swap a non-leap year with a leap-year).
> >
> > Any help GREATLY appreciated.|||JTWhaler (jonahturnedwhaler@.yahoo.com) writes:
> This is very helpful, thank-you.
> Now that I have this list of table names having counts of datetime
> columns, any suggestions as to discovering *which* columns have the
> datetime, and how I can begin printing these particular columns
> values?
>
Here is a query that returns the column names as well:
select o.name, c.name
from sysobjects o
join syscolumns c on o.id = c.id
join systypes t on c.type = t.type
where t.name in ('datetime', 'smalldatetime')
order by o.name, c.name
(I assuming that everything is owned by dbo.)
To run some general code that goes through all columns, you would probably
have to use dynamic SQL to build statements on the fly.
If you are not acquainted with dynamic SQL, I have an article on my
web site, http://www.sommarskog.se/dynamic_sql.html. The article may
seem dauntingly long, but the article is trying to tell you that
you should use this feature with care. But this seems to be a good
occasion for using dynamic SQL.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9466475B2A41Yazorman@.127.0.0.1>...
> JTWhaler (jonahturnedwhaler@.yahoo.com) writes:
> > This is very helpful, thank-you.
> >
> > Now that I have this list of table names having counts of datetime
> > columns, any suggestions as to discovering *which* columns have the
> > datetime, and how I can begin printing these particular columns
> > values?
> >
> Here is a query that returns the column names as well:
> select o.name, c.name
> from sysobjects o
> join syscolumns c on o.id = c.id
> join systypes t on c.type = t.type
> where t.name in ('datetime', 'smalldatetime')
> order by o.name, c.name
> (I assuming that everything is owned by dbo.)
> To run some general code that goes through all columns, you would probably
> have to use dynamic SQL to build statements on the fly.
> If you are not acquainted with dynamic SQL, I have an article on my
> web site, http://www.sommarskog.se/dynamic_sql.html. The article may
> seem dauntingly long, but the article is trying to tell you that
> you should use this feature with care. But this seems to be a good
> occasion for using dynamic SQL.
Thank-you very much. This is helpful, and I plan to read the dynamic SQL article.