How to get the value of script of various SQLDMO(version 8.0) objects

Hello,
I am working on a project where I want to access and modify some objects of Microsoft SQL 2000 database.
Would someone help me how to do this? I need following informations;
- Script of the checks of a table
- Script of the Indexes of a table
- Script of Key object(Primary or Forign key)
- Script of Permissions of View and Stored procedure

The following code returns no result;

MyTabelClass mytabelClass = new MyTabelClass();

mytabelClass.Naam = table.Name;

mytabelClass.ChecksScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_Checks,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

mytabelClass.IndexesScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_Indexes,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

Thanks in advance.
With regards,

Your script is incorrect. Instead of accessing an existing object, you create a new one :

MyTabelClass mytabelClass = new MyTabelClass(); create a new instance.

See this sample:

Dim svr As New SQLServer
svr.Name = "."
svr.LoginSecure = True
svr.Connect

Dim t As Table

Set t = svr.Databases("pubs").Tables("authors")

MsgBox t.Script(SQLDMOScript_DRI_Checks, "", "", SQLDMOScript2_Default)
MsgBox t.Script(SQLDMOScript_Indexes, "", "", SQLDMOScript2_Default)

Groeten/Greetings

How to get the value of script of various SQLDMO(version 8.0) objects

Hello,
I am working on a project where I want to access and modify some objects of Microsoft SQL 2000 database.
Would someone help me how to do this? I need following informations;
- Script of the checks of a table
- Script of the Indexes of a table
- Script of Key object(Primary or Forign key)
- Script of Permissions of View and Stored procedure

The following code returns no result;

MyTabelClass mytabelClass = new MyTabelClass();

mytabelClass.Naam = table.Name;

mytabelClass.ChecksScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_Checks,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

mytabelClass.IndexesScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_Indexes,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

Thanks in advance.
With regards,

Your script is incorrect. Instead of accessing an existing object, you create a new one :

MyTabelClass mytabelClass = new MyTabelClass(); create a new instance.

See this sample:

Dim svr As New SQLServer
svr.Name = "."
svr.LoginSecure = True
svr.Connect

Dim t As Table

Set t = svr.Databases("pubs").Tables("authors")

MsgBox t.Script(SQLDMOScript_DRI_Checks, "", "", SQLDMOScript2_Default)
MsgBox t.Script(SQLDMOScript_Indexes, "", "", SQLDMOScript2_Default)

Groeten/Greetings

How to get the list

I have a need to get the list of all dependencies
various objects have within the database.
I know in EM I can do it for individual objects, I would
like to know if there is a way to get dependencies
for all tables and store it as a document for future
reference.
Thank you for your help!
-LindaLinda,
this is a cheap-and-cheerful way to do it:
sp_msforeachtable "sp_depends '?'; select '?' as Tablename"
However the format of the output is not perfect. I'd be tempted to create
your own cursor to return nicely formatted data.
EG something like this:
set nocount on
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @.tablename sysname
FETCH NEXT FROM tables_cursor INTO @.tablename
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
select '**************************************************************'
select @.tablename as 'Tablename'
EXEC ('sp_depends ' + @.tablename )
FETCH NEXT FROM tables_cursor INTO @.tablename
END
PRINT 'Finished!'
DEALLOCATE tables_cursor
Regards,
Paul Ibison|||Linda,
SQL Server object dependencies are stored in a table called sysdepends.
However, this table is not maintained accurately under all circumstances
so you often find it to be incorrect.
Some related articles:
BUG: Recreating a Table Causes sysdepends to Become Invalid
http://support.microsoft.com/?id=115333
BUG: SP_Depends Does Not List Triggers
http://support.microsoft.com/?id=180490
BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
Sp_depends
http://support.microsoft.com/?id=201846
Displaying Dependencies
http://www.microsoft.com/sql/techinfo/tips/development/displayingdependencies.asp
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Linda wrote:
> I have a need to get the list of all dependencies
> various objects have within the database.
> I know in EM I can do it for individual objects, I would
> like to know if there is a way to get dependencies
> for all tables and store it as a document for future
> reference.
> Thank you for your help!
> -Linda|||Mark,
Thank you for the reply!
I do notice errors as you have mentioned.
What about EM? Does it show it correctly there?
What are the repurcussions if some dependencies don't
appear even in EM?
Thank you,
-Linda
>--Original Message--
>Linda,
>SQL Server object dependencies are stored in a table
called sysdepends.
>However, this table is not maintained accurately under
all circumstances
>so you often find it to be incorrect.
>Some related articles:
>BUG: Recreating a Table Causes sysdepends to Become
Invalid
>http://support.microsoft.com/?id=115333
>BUG: SP_Depends Does Not List Triggers
>http://support.microsoft.com/?id=180490
>BUG: Reference to Deferred Object in Stored Procedure
Will Not Show in
>Sp_depends
>http://support.microsoft.com/?id=201846
>Displaying Dependencies
>http://www.microsoft.com/sql/techinfo/tips/development/di
splayingdependencies.asp
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>Linda wrote:
>> I have a need to get the list of all dependencies
>> various objects have within the database.
>> I know in EM I can do it for individual objects, I
would
>> like to know if there is a way to get dependencies
>> for all tables and store it as a document for future
>> reference.
>> Thank you for your help!
>> -Linda
>.
>|||Paul,
Thanks for your detailed reply! It helped me.
-Linda
>--Original Message--
>Linda,
>this is a cheap-and-cheerful way to do it:
>sp_msforeachtable "sp_depends '?'; select '?' as
Tablename"
>However the format of the output is not perfect. I'd be
tempted to create
>your own cursor to return nicely formatted data.
>EG something like this:
>set nocount on
>DECLARE tables_cursor CURSOR
> FOR
> SELECT name FROM sysobjects WHERE type = 'U'
>OPEN tables_cursor
>DECLARE @.tablename sysname
>FETCH NEXT FROM tables_cursor INTO @.tablename
>WHILE (@.@.FETCH_STATUS <> -1)
>BEGIN
>
select '**************************************************
************'
> select @.tablename as 'Tablename'
> EXEC ('sp_depends ' + @.tablename )
> FETCH NEXT FROM tables_cursor INTO @.tablename
>END
>PRINT 'Finished!'
>DEALLOCATE tables_cursor
>Regards,
>Paul Ibison
>
>.
>

How to get table info from RID deadlock ?

Hi There

I am trying to troubleshoot deadlock issues.

The dead lock is with a RID.

Now from various links i have established that you have to use DBCC PAGE() with relevant info to get more information, i get back a object and index id, m_objid.

However i dont know how to get the tablename form here, msdn simply says that it is an allocation id not and actual table id, so how can i from here get to table information?

All i am trying to figure out is which table is affected fromt he RID dealock, but that does not seem to be a simple process.

Thanx

Anyone please ?

I have had the deadlock again:

Info is as follows:

RID 7,1, 6937169, 1

So i do the following to get the m_objid.

Dbcc traceon(3604)

Go

Dbcc page(7,1, 6937169, 0)

Once i have the m_objid , i do the following select object_name(m_objid), this returns null ?

Someone please tell me how on earth do you get the table name from the deadlock info involving a RID ?

Thanx

|||In SQL 2005, you can get the object id from Metadata: ObjectId when you execute the DBCC PAGE command.

Thanks,|||

Hi Marcel

I have already stated that, what i am asking is how do you get from the allocation object id provided by DBCC PAGE to a actual table name, select object_name(m_objid) , using the object id returned by DBCC PAGE returns null ?

|||My Apologies Marcel, i see you meant MetaData object id, that works, thanx a million.

How to get table info from RID deadlock ?

Hi There

I am trying to troubleshoot deadlock issues.

The dead lock is with a RID.

Now from various links i have established that you have to use DBCC PAGE() with relevant info to get more information, i get back a object and index id, m_objid.

However i dont know how to get the tablename form here, msdn simply says that it is an allocation id not and actual table id, so how can i from here get to table information?

All i am trying to figure out is which table is affected fromt he RID dealock, but that does not seem to be a simple process.

Thanx

Anyone please ?

I have had the deadlock again:

Info is as follows:

RID 7,1, 6937169, 1

So i do the following to get the m_objid.

Dbcc traceon(3604)

Go

Dbcc page(7,1, 6937169, 0)

Once i have the m_objid , i do the following select object_name(m_objid), this returns null ?

Someone please tell me how on earth do you get the table name from the deadlock info involving a RID ?

Thanx

|||In SQL 2005, you can get the object id from Metadata: ObjectId when you execute the DBCC PAGE command.

Thanks,|||

Hi Marcel

I have already stated that, what i am asking is how do you get from the allocation object id provided by DBCC PAGE to a actual table name, select object_name(m_objid) , using the object id returned by DBCC PAGE returns null ?

|||My Apologies Marcel, i see you meant MetaData object id, that works, thanx a million.

How to get service accounts for 150 servers

Hi Everyone.

I have 150 SQL servers (2000 MSDE). They all run using various domain accounts as their service logins. Is there an automated way to find out those service logins? Maybe a query I could run on each server?

I really do not want to go to each of those 150 servers and look at their properties manualy! :S

Any help would be greatly appreciated! Thank you.Something around the master.dbo.sysusers and/or master.dbo.syslogins
table(s) ?|||I think that you want the startname value from this (http://www.microsoft.com/technet/scriptcenter/scripts/os/services/ossvvb08.mspx) script.

-PatP|||I think that you want the startname value from this (http://www.microsoft.com/technet/scriptcenter/scripts/os/services/ossvvb08.mspx) script.

-PatP

Yeah, I would think that WMI would be your best bet. Dump the list of servers into a text file (or XML). Open that file using the File system object and spin through (using oFile.ReadLine). Then feed the string value (the computer name) into a function that returns the name of of the service account you are looking for. Dump that into a separate text file (or a database).

Regards,

hmscott|||Thank you everyone for responding. I got an answer at experts-exchange. The following query will return the service account:

declare @.rc int,
@.dir nvarchar(4000)
exec @.rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'System\CurrentControlSet\S ervices\MSSQLServer\',N'ObjectName', @.dir output, 'no_output'
select @.dir

I've modified it put the result in the central table and then ran it through a SQL loop. Worked perfectly! :D

WMI would have worked as well, but this solution took just 10 min to implement.

P.S. Had no idea that something like xp_instance_regread existed. Is too much to hope that sql server 2005 will have better documentation? :-)|||That Transact-SQL code will work nicely as long as you only need the default instance of SQL 2000.

-PatP|||That Transact-SQL code will work nicely as long as you only need the default instance of SQL 2000.

-PatP

Good point, Pat. For named instances the command will need to be modified:

declare @.rc int,
@.dir nvarchar(4000)
exec @.rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'System\CurrentControlSet\S ervices\MSSQL$Your_Instance_Name\',N'ObjectName', @.dir output, 'no_output'
select @.dir