How to determine which file belongs to which filegroup and whichdatabase

Hi,
I am writing a C++ program that will take backup of .mdf files. I will
be provided with .mdf files. And for that mdf file, I need to know
the database name and file group name associated with this .mdf file.
How can I do it? Please help me to sort this out.
Thanks
Iftekhar
<chayancse@.gmail.com> wrote in message
news:3b6adb60-0322-4f4b-973b-a9fbee6dd0b0@.e10g2000prf.googlegroups.com...
> Hi,
> I am writing a C++ program that will take backup of .mdf files. I will
> be provided with .mdf files. And for that mdf file, I need to know
> the database name and file group name associated with this .mdf file.
> How can I do it? Please help me to sort this out.
> Thanks
> Iftekhar
You don't need that information to do backups because the BACKUP command
will take care of everything for you. Don't attempt to access or backup the
MDF/LDF files directly because you can't guarantee a good backup that way
without taking the database offline.
The information you are looking for is in these three system tables:
sys.database_files
sys.filegroups
sys.data_spaces
David Portas
|||On Feb 7, 12:18 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> <chayan...@.gmail.com> wrote in message
> news:3b6adb60-0322-4f4b-973b-a9fbee6dd0b0@.e10g2000prf.googlegroups.com...
>
>
> You don't need that information to do backups because the BACKUP command
> will take care of everything for you. Don't attempt to access or backup the
> MDF/LDF files directly because you can't guarantee a good backup that way
> without taking the database offline.
> The information you are looking for is in these three system tables:
> sys.database_files
> sys.filegroups
> sys.data_spaces
> --
> David Portas
Dear David,
Thanks for your reply. The system tables you have mentioned will help
me to determine the physical files associated with a database. But i
need to do the reverse task. I need to determine the logical database
name from a physical file name. Can you help me with this?
Also, is there any way to directly backup .mdf files without knowing
the database name?
Thanks
Iftekhar
|||Hi Iftekhar,
As David mentioned, a backup of the files of an online database is not a
valid backup.
Use the T-SQL BACKUP command instead.
Hope this helps,
Ben Nevarez
"chayancse@.gmail.com" wrote:

> On Feb 7, 12:18 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> Dear David,
> Thanks for your reply. The system tables you have mentioned will help
> me to determine the physical files associated with a database. But i
> need to do the reverse task. I need to determine the logical database
> name from a physical file name. Can you help me with this?
> Also, is there any way to directly backup .mdf files without knowing
> the database name?
> Thanks
> Iftekhar
>
|||Hi

> I need to determine the logical database
> name from a physical file name. Can you help me with this?
It can be a problem. I have seen ( only one time) when the person rename the
physical file name and it was not associated with the name of database
<chayancse@.gmail.com> wrote in message
news:e2777758-af6e-4263-8f06-89c3fa692a33@.m34g2000hsb.googlegroups.com...
> On Feb 7, 12:18 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> Dear David,
> Thanks for your reply. The system tables you have mentioned will help
> me to determine the physical files associated with a database. But i
> need to do the reverse task. I need to determine the logical database
> name from a physical file name. Can you help me with this?
> Also, is there any way to directly backup .mdf files without knowing
> the database name?
> Thanks
> Iftekhar
|||<chayancse@.gmail.com> wrote in message
news:e2777758-af6e-4263-8f06-89c3fa692a33@.m34g2000hsb.googlegroups.com...
> Dear David,
> Thanks for your reply. The system tables you have mentioned will help
> me to determine the physical files associated with a database. But i
> need to do the reverse task. I need to determine the logical database
> name from a physical file name. Can you help me with this?
> Also, is there any way to directly backup .mdf files without knowing
> the database name?
>
That doesn't make much sense to me. You should be backing up DATABASES not
FILES. Why would you want to backup a file without knowing what database it
belonged to?
Find the database name here:
SELECT
DB_NAME(database_id) database_name,
physical_name
FROM master.sys.master_files;
Now use BACKUP to backup the database. Don't attempt to backup the file. If
you don't understand the difference then please study the topics on Backup
and Restore in Books Online.
David Portas
|||On Feb 8, 1:30 am, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> <chayan...@.gmail.com> wrote in message
> news:e2777758-af6e-4263-8f06-89c3fa692a33@.m34g2000hsb.googlegroups.com...
>
>
>
> That doesn't make much sense to me. You should be backing up DATABASES not
> FILES. Why would you want to backup a file without knowing what database it
> belonged to?
> Find the database name here:
> SELECT
> DB_NAME(database_id) database_name,
> physical_name
> FROM master.sys.master_files;
> Now use BACKUP to backup the database. Don't attempt to backup the file. If
> you don't understand the difference then please study the topics on Backup
> and Restore in Books Online.
> --
> David Portas
Thanks David. My task has been done. It's really unusual to backup
files instead of database. But I am developing a file backup system.
That will backup all normal files. If any of them are database files,
still they must be backed up.
Anyway, it's your post that actually helped me. So thanks a lot.
Thanks
Iftekhar

How to determine actual constraint name by passing a column name

I need a query in which I can pass in a column name and table name and will
be provided all constraints ( all types ) for that given column in that
table. I've created and found several queries that work for various types
but have not found one that does what I need for Unique constraints. I have
the following for FKs, but it doesn't work for UQs
select db_name() as DATABASE_name
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid) as OWNER
,c_obj.name as CONSTRAINT_NAME
,col.name as COLUMN_NAME
,col.colid as ORDINAL_POSITION
,c_obj.xtype as XTYPE
from
sysobjects c_obj
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id and con.colid = col.colid
where
c_obj.xtype = 'F'
ORDER BY t_obj.name
If anyone can point out what I am missing, I'd really appreciate it.
Thanks
RachelDid you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN
_USAGE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
>I need a query in which I can pass in a column name and table name and will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I ha
ve
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Ken,
Your query will not pick up unique and primary key constraints - they have
no data in syscolumns. To "fix" your query, make the join with syscolumns a
LEFT join:
> LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
Now, however, your 'Column' column will be null for PK and UQ constraints,
because they are implemented as indexes and their columns are stored in
sysindexes, not sysconstraints.
(BTW, the INFORMATION_SCHEMA views will not return any data regarding
default constraints, so I think you're on a better track by going straight
to the system tables.)
Basically, your start is correct in that
select *
from sysobjects
where parent_obj = object_id('<tablename>')
will return all of a table's constraints.
Since you want column information for each constraint, you're also going to
have problems when a constraint (PK, UQ, FK) covers more than one of a
table's columns. You need to decide whether to have multiple column
constraints come back as multiple rows or as a comma-delimited string (like
sp_helpconstraint or sp_helpindex do)
Do you like the output of sp_helpconstraint? If so, I would recommend you
just rewrite sp_helpconstraint, modifying it to take a table name and owner
as parameters, add the appropriate filter, and make it return only one
result set and just the resulting columns you want. Be sure to test on
multiple-column constraints.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> I need a query in which I can pass in a column name and table name and
will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I
have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Of course I meant rewrite sp_helpconstraint as a NEW stored procedure, with
a different name!
Ron
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23GUlV7U7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Ken,
> Your query will not pick up unique and primary key constraints - they have
> no data in syscolumns. To "fix" your query, make the join with syscolumns
a
> LEFT join:
> Now, however, your 'Column' column will be null for PK and UQ constraints,
> because they are implemented as indexes and their columns are stored in
> sysindexes, not sysconstraints.
> (BTW, the INFORMATION_SCHEMA views will not return any data regarding
> default constraints, so I think you're on a better track by going straight
> to the system tables.)
> Basically, your start is correct in that
> select *
> from sysobjects
> where parent_obj = object_id('<tablename>')
> will return all of a table's constraints.
> Since you want column information for each constraint, you're also going
to
> have problems when a constraint (PK, UQ, FK) covers more than one of a
> table's columns. You need to decide whether to have multiple column
> constraints come back as multiple rows or as a comma-delimited string
(like
> sp_helpconstraint or sp_helpindex do)
> Do you like the output of sp_helpconstraint? If so, I would recommend you
> just rewrite sp_helpconstraint, modifying it to take a table name and
owner
> as parameters, add the appropriate filter, and make it return only one
> result set and just the resulting columns you want. Be sure to test on
> multiple-column constraints.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> will
types[vbcol=seagreen]
> have
>

How to determine actual constraint name by passing a column name

I need a query in which I can pass in a column name and table name and will
be provided all constraints ( all types ) for that given column in that
table. I've created and found several queries that work for various types
but have not found one that does what I need for Unique constraints. I have
the following for FKs, but it doesn't work for UQs
selectdb_name()as DATABASE_name
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid)as OWNER
,c_obj.nameas CONSTRAINT_NAME
,col.nameas COLUMN_NAME
,col.colidas ORDINAL_POSITION
,c_obj.xtypeas XTYPE
from
sysobjectsc_obj
join sysobjectst_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id= con.constid
join syscolumnscol on t_obj.id = col.id and con.colid = col.colid
where
c_obj.xtype= 'F'
ORDER BY t_obj.name
If anyone can point out what I am missing, I'd really appreciate it.
Thanks
Rachel
Did you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN_USAGE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
>I need a query in which I can pass in a column name and table name and will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel
|||Ken,
Your query will not pick up unique and primary key constraints - they have
no data in syscolumns. To "fix" your query, make the join with syscolumns a
LEFT join:
> LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
Now, however, your 'Column' column will be null for PK and UQ constraints,
because they are implemented as indexes and their columns are stored in
sysindexes, not sysconstraints.
(BTW, the INFORMATION_SCHEMA views will not return any data regarding
default constraints, so I think you're on a better track by going straight
to the system tables.)
Basically, your start is correct in that
select *
from sysobjects
where parent_obj = object_id('<tablename>')
will return all of a table's constraints.
Since you want column information for each constraint, you're also going to
have problems when a constraint (PK, UQ, FK) covers more than one of a
table's columns. You need to decide whether to have multiple column
constraints come back as multiple rows or as a comma-delimited string (like
sp_helpconstraint or sp_helpindex do)
Do you like the output of sp_helpconstraint? If so, I would recommend you
just rewrite sp_helpconstraint, modifying it to take a table name and owner
as parameters, add the appropriate filter, and make it return only one
result set and just the resulting columns you want. Be sure to test on
multiple-column constraints.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> I need a query in which I can pass in a column name and table name and
will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I
have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel
|||Of course I meant rewrite sp_helpconstraint as a NEW stored procedure, with
a different name!
Ron
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23GUlV7U7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Ken,
> Your query will not pick up unique and primary key constraints - they have
> no data in syscolumns. To "fix" your query, make the join with syscolumns
a
> LEFT join:
> Now, however, your 'Column' column will be null for PK and UQ constraints,
> because they are implemented as indexes and their columns are stored in
> sysindexes, not sysconstraints.
> (BTW, the INFORMATION_SCHEMA views will not return any data regarding
> default constraints, so I think you're on a better track by going straight
> to the system tables.)
> Basically, your start is correct in that
> select *
> from sysobjects
> where parent_obj = object_id('<tablename>')
> will return all of a table's constraints.
> Since you want column information for each constraint, you're also going
to
> have problems when a constraint (PK, UQ, FK) covers more than one of a
> table's columns. You need to decide whether to have multiple column
> constraints come back as multiple rows or as a comma-delimited string
(like
> sp_helpconstraint or sp_helpindex do)
> Do you like the output of sp_helpconstraint? If so, I would recommend you
> just rewrite sp_helpconstraint, modifying it to take a table name and
owner[vbcol=seagreen]
> as parameters, add the appropriate filter, and make it return only one
> result set and just the resulting columns you want. Be sure to test on
> multiple-column constraints.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> will
types
> have
>
sql