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 objects signed by certificate

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates what so ever. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

I'm not sure this posted correctly the first time...

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates. There is sys.certificates which doesn't help as far as determining what it has been applied to and a syscerts that I came across in sys.columns, but can't actually query. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'my_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'your_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

Thanks, that is exactly what I was looking for.

How to determine objects signed by a certificate

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates what so ever. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

I'm not sure this posted correctly the first time...

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates. There is sys.certificates which doesn't help as far as determining what it has been applied to and a syscerts that I came across in sys.columns, but can't actually query. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'my_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'your_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

Thanks, that is exactly what I was looking for.

How to detect whether SMO is installed?

Hi, folks --
I am working on an install bootstrapper that (among other things)
will install Server Management Objects (SQLServer2005_XMO.msi from SQL
Server 2005 Feature Pack) iff it's not already installed.
So, how to detect programmatically whether or not it's already
installed?
What files/version/registry keys/usual suspects should I be looking
for?
Thanks in advance for your help.
-- Davidson
Hello n7dai@.comcast.net,
I normally look for C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.d ll.
I believe current-most is 9.0.3042.0.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||What about:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=577731&SiteID=1
Jens K. Suessmeyer.
http://www.sqlserver2005.de
<n7dai@.comcast.net> wrote in message
news:1175273193.753957.182290@.e65g2000hsc.googlegr oups.com...
> Hi, folks --
> I am working on an install bootstrapper that (among other things)
> will install Server Management Objects (SQLServer2005_XMO.msi from SQL
> Server 2005 Feature Pack) iff it's not already installed.
> So, how to detect programmatically whether or not it's already
> installed?
> What files/version/registry keys/usual suspects should I be looking
> for?
> Thanks in advance for your help.
> -- Davidson
>

How to detect any changes made in Dev DB compared to Live DB

Hi,
I was just wondering if there's any way to detect what database objects
were lately modified in Dev database as compared to the similar one in Live
database.
When I develop in Dev datatabse, its very tough to keep track of all the
activities & changes that I do.
Is there a T-SQL where I can run it in a snapshot and know what tables,
columns, views, stored procs, triggers, user-defined function, etc. differ
than that in Live database.
This way, I know which objects to publish from Dev to Live.
Thanks,
Sam.
--
Message posted via http://www.sqlmonster.comNothing directly that comes with SQL Server. You can try
third party tools to do this - one is SQL Compare from Red
Gate:
http://www.red-gate.com/sql/summary.htm
The only free thing I know of is a script on
sql-server-performance.com which compares databases. Check
the following article:
http://www.sql-server-performance.com/vg_database_comparison_sp.asp
-Sue
On Tue, 15 Mar 2005 21:47:54 GMT, "Sameer Premji via
SQLMonster.com" <forum@.SQLMonster.com> wrote:
>Hi,
>I was just wondering if there's any way to detect what database objects
>were lately modified in Dev database as compared to the similar one in Live
>database.
>When I develop in Dev datatabse, its very tough to keep track of all the
>activities & changes that I do.
>Is there a T-SQL where I can run it in a snapshot and know what tables,
>columns, views, stored procs, triggers, user-defined function, etc. differ
>than that in Live database.
>This way, I know which objects to publish from Dev to Live.
>Thanks,
>Sam.

How to deploy assembly from remote system, without using VS.NET

Hi

I'm developing a tool to deploy the CLR objects from any where to the server

But I'm unable to deploy from remote system to the server.

I'm getting the error like

CREATE ASSEMBLY failed because it could not open the physical file 'D:\Working\DBS2005V2\GL\GLREPORTS\DBS.GL.RM.SQL\bin\Debug\sample.dll': 3(The system cannot find the path specified.).

when I try to deploy using

EXEC dbo.sp_executesql @.statement = N' CREATE ASSEMBLY [DBS.GL.RM.SQL]

FROM ''D:\Working\DBS2005V2\GL\GLREPORTS\DBS.GL.RM.SQL\bin\Debug\sample.dll''

WITH PERMISSION_SET = SAFE'

can anybody tell how to generate assembly_bits for assembly

the VS.NEt is generating assembly bits instead of physical path to deploy the assembly on the remote server.

as

CREATE ASSEMBLY [sample]

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215 ..

WITH PERMISSION_SET = SAFE

But nothing is provided reg this in MSDN.

will u help me in handle this please

Thank u

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=288119&SiteID=1 for code that will allow you to generate the assembly bits string.