How to determine what object is on what filegroup?

I have a vendor product that has 15 filegroups and I would like to determine
what objects(tables,indx..) reside on what filegroup? I used generate
script and looking at each object to see where it was created and this seems
the hard way to go about it. Is there a sp or a script that would list the
objects and where they exist with having to dig through these items.
TIA
C
CD
This script has written by Itzik Ben-Gan
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
alter database test modify filegroup user_fg default
create table t4(col1 int)
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'),
object_id('t3'),object_id('t4'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
drop database test
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:uC38SPPQFHA.3880@.tk2msftngp13.phx.gbl...
> I have a vendor product that has 15 filegroups and I would like to
determine
> what objects(tables,indx..) reside on what filegroup? I used generate
> script and looking at each object to see where it was created and this
seems
> the hard way to go about it. Is there a sp or a script that would list
the
> objects and where they exist with having to dig through these items.
> TIA
> C
>
sql

How to determine what object is on what filegroup?

I have a vendor product that has 15 filegroups and I would like to determine
what objects(tables,indx..) reside on what filegroup? I used generate
script and looking at each object to see where it was created and this seems
the hard way to go about it. Is there a sp or a script that would list the
objects and where they exist with having to dig through these items.
TIA
CCD
This script has written by Itzik Ben-Gan
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
alter database test modify filegroup user_fg default
create table t4(col1 int)
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'),
object_id('t3'),object_id('t4'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
drop database test
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:uC38SPPQFHA.3880@.tk2msftngp13.phx.gbl...
> I have a vendor product that has 15 filegroups and I would like to
determine
> what objects(tables,indx..) reside on what filegroup? I used generate
> script and looking at each object to see where it was created and this
seems
> the hard way to go about it. Is there a sp or a script that would list
the
> objects and where they exist with having to dig through these items.
> TIA
> C
>

How to determine what object is on what filegroup?

I have a vendor product that has 15 filegroups and I would like to determine
what objects(tables,indx..) reside on what filegroup? I used generate
script and looking at each object to see where it was created and this seems
the hard way to go about it. Is there a sp or a script that would list the
objects and where they exist with having to dig through these items.
TIA
CCD
This script has written by Itzik Ben-Gan
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
alter database test modify filegroup user_fg default
create table t4(col1 int)
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'),
object_id('t3'),object_id('t4'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
drop database test
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:uC38SPPQFHA.3880@.tk2msftngp13.phx.gbl...
> I have a vendor product that has 15 filegroups and I would like to
determine
> what objects(tables,indx..) reside on what filegroup? I used generate
> script and looking at each object to see where it was created and this
seems
> the hard way to go about it. Is there a sp or a script that would list
the
> objects and where they exist with having to dig through these items.
> TIA
> C
>

How to determine compiled proc size?

How do I determine the amount of server memory a stored procedure (or any
other object) takes up?http://www.sql-server-performance.com/rd_data_cache.asp gives some
information regarding querying the syscacheobjects system table and what
each row in the table means. You should be able to use that to derive
the number of pages of memory used with many kinds of server objects.
Good luck,
Tony Sebion
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:063D9CCE-5864-4372-BBFA-FB1DCE8F6472@.microsoft.com:

> How do I determine the amount of server memory a stored procedure (or any
> other object) takes up?|||There's also an undocumented DBCC command: DBCC MEMOBJLIST but it requires
starting the service with the T3654 flag and it doesn't show what's in AWE
memory.
"Tony Sebion" wrote:

> http://www.sql-server-performance.com/rd_data_cache.asp gives some
> information regarding querying the syscacheobjects system table and what
> each row in the table means. You should be able to use that to derive
> the number of pages of memory used with many kinds of server objects.
> Good luck,
> Tony Sebion
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:063D9CCE-5864-4372-BBFA-FB1DCE8F6472@.microsoft.com:
>
>

How to delete the role by using AMO(Analysis Management Object)?

I want to delete the role by using AMO, but what I find is only a way that how to create it.

following sample is deleting the members of the role :

Code Snippet

Dim ServerName As Server 'Connect OLAP Server
Dim db As Database 'Database of OLAP Server

Dim role As Role

role = db.Roles.Item(0)
role.Members.Clear()
role.Update()

So I think that deleting the roles of database is like following :

Code Snippet

db.Roles.Clear()

db.Update()

It's wrong. It can't delete roles of a database after executing.

Please tell me what I shall do or where I can find these infoemation, thanks!!

Here's a code sample from one of our developers that does this. (Thanks for the code, Jason.) Keep in mind that in this code, we were looking for Roles that met a naming standard. It's a long story, but you may want to skip that step. Still, I kept it in here (and only changed the naming pattern) so I can insure the code still works with minimal effort. Also, keep in mind this code was written for an SSIS package so you will see some odd ball references in there.

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

Dim roleCollection As New ArrayList()

' FIND ALL OF THE ROLES IN THE ANALYSIS SERVICES DB THAT MATCH THE NAMING PATTERN
For Each currentRole As Role In database.Roles
If (currentRole.Name.StartsWith("XYZ")) Then
' BECAUSE YOU CAN'T DELETE AN ITEM IN A COLLECTION, ADD TO THE roleCollection ARRAY LIST
roleCollection.Add(currentRole)
End If
Next

' DELETE ALL ROLES IN THE roleCollection
For Each currentObj As Role In roleCollection

'DROP OPTION OF AlterOrDeleteDependents SHOULD REMOVE ANY ASSOCIATED PERMISSIONS WITH THIS ROLE
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

|||

Thanks for your answer~~

I can delete the role now.But I find that I can skip the step of ArrayList() and delete the role.

I don't know what a risk has in these statement, like following code :

If you know that. Could you tell me,please? Thanks!!

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' DELETE A ROLES
Dim currentObj As Role

currentObj.database.Roles.FinByName("XYZ")
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

How to delete the role by using AMO(Analysis Management Object)?

I want to delete the role by using AMO, but what I find is only a way that how to create it.

following sample is deleting the members of the role :

Code Snippet

Dim ServerName As Server 'Connect OLAP Server
Dim db As Database 'Database of OLAP Server

Dim role As Role

role = db.Roles.Item(0)
role.Members.Clear()
role.Update()

So I think that deleting the roles of database is like following :

Code Snippet

db.Roles.Clear()

db.Update()

It's wrong. It can't delete roles of a database after executing.

Please tell me what I shall do or where I can find these infoemation, thanks!!

Here's a code sample from one of our developers that does this. (Thanks for the code, Jason.) Keep in mind that in this code, we were looking for Roles that met a naming standard. It's a long story, but you may want to skip that step. Still, I kept it in here (and only changed the naming pattern) so I can insure the code still works with minimal effort. Also, keep in mind this code was written for an SSIS package so you will see some odd ball references in there.

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

Dim roleCollection As New ArrayList()

' FIND ALL OF THE ROLES IN THE ANALYSIS SERVICES DB THAT MATCH THE NAMING PATTERN
For Each currentRole As Role In database.Roles
If (currentRole.Name.StartsWith("XYZ")) Then
' BECAUSE YOU CAN'T DELETE AN ITEM IN A COLLECTION, ADD TO THE roleCollection ARRAY LIST
roleCollection.Add(currentRole)
End If
Next

' DELETE ALL ROLES IN THE roleCollection
For Each currentObj As Role In roleCollection

'DROP OPTION OF AlterOrDeleteDependents SHOULD REMOVE ANY ASSOCIATED PERMISSIONS WITH THIS ROLE
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

|||

Thanks for your answer~~

I can delete the role now.But I find that I can skip the step of ArrayList() and delete the role.

I don't know what a risk has in these statement, like following code :

If you know that. Could you tell me,please? Thanks!!

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' DELETE A ROLES
Dim currentObj As Role

currentObj.database.Roles.FinByName("XYZ")
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

how to delete object Data Source for local (rdlc) report?

I've created a couple of data sources and now would like to remove one on a local (*.rdlc) report, how do I go about doing this? Just to clarify, these data sources are for a local report in a smart client application (report made in VS2005 Report Designer, and not SQL Server 2005 Report Designer), they are found in the report's Data Sources window (not the applications) and can be accessed/assigned by the local report (Report > Data Sources...) - where you can remove the data source from being on the report, but not completely remove from the project.

Any help is appreciated.
Thanks.
PeterSorry, it was a newbie question. Found why I couldn't delete the extra data source, it was an XSD data source that I created outside of the Data Source folder, and simply lost track of it.

How to define an object in common place instead of every SP in SQL Server

hi,

I want to define a linked server object in one common place instead of every SP. (Because the UAT DB or SIT DB maybe the linked server with different name)

For Oracle we can define one common object in Package like following:

CREATE OR REPLACE PACKAGE FMS_PKG IS

TYPE T_CURSOR IS REF CURSOR;

END FMS_PKG;

Then we can use it in SP like FMS_PKG.T_CURSOR. So I wonder whether SQL Server 2005 has corresponding place can do this.

Thanks

Bily Jiang

Assuming that your Linked Server is another instance of SQL Server then you could create a Linked Server, named, for example, 'MyLinkedServer', and reference this in your stored procs / Views etc...

You can then use the CliConfg.exe application to create an Alias on the server hosting your primary SQL Server instance. Name the Alias 'MyLinkedServer' and specify the Server Alias (the instance of SQL Server to which your Linked Server should point) as appropriate to your environment.

This way you do not have to make changes to the code when you progress through each of the environments - all you have to do is update the Server Alias on the server and any Linked Server security settings on the primary SQL Server instance.

Chris

|||

Yes, I have found this tool.

Could you tell me how to use this tool to create one Linked Server Alias?

After I open it, I click Alias tab --> click Add --> should choose which option?

Thanks

Bily Jiang

|||

It depends on which network protocol you are using.

We use TCP/IP, so I would select 'TCP/IP', enter the Linked Server name in the Server Alias text box (e.g. MyLinkedServer), then enter the name of the server (plus instance name if necessary) that you wish to connect to in the 'Server Name' text box.

Chris

|||If you are using SQL Server 2005 you can create Synonyms for each table, view, procedure or function you are accessing via the linked server. Then you will just use the Synonym name to reference the object on the remote server, not the linked server syntax.|||

Thank you so much.

I feel synonym is the simplest resolution especially when we just access several remoting tables.

But I think package is the advantage of Oracle.

Regards

Ren Jie