Transact SQL :: List All File-groups For All Databases?

May 27, 2009

I would like to SELECT all filegroup on an SQL server instance, is that possible?Or only per database?

View 21 Replies


ADVERTISEMENT

How To List All File Groups

Jul 20, 2004

How to list all the file groups

View 1 Replies View Related

Transact SQL :: Backup Primary File Groups With No Procedures?

Aug 20, 2015

I would have to handover my DB with only Primary tables to client as part of SLA. 

I am planning to keep these primary tables on a secondary file-group but how-ever, I will still have my procedures on primary file-group. 

How can I accomplish this with client having no exposure to my stored procedures. 

View 2 Replies View Related

How To Get List Of Windows Users And Groups?

Jan 9, 2008

I want to map users/groups of a .NET application which uses MS SQL Server to Windows users/groups. Then grant pemission to the Windows users/groups to access the MS SQL Server.

If possible, I want to list Windows Users and Groups regardless of whether Active Directory is used or not.

View 1 Replies View Related

Extra Blank Lines When Using Multiple List For Groups.

Oct 9, 2007

I am working on conversion of Crystal reports to SSRS. The existing report has two level of grouping. So I have included 2 list inside a third list and then done the grouping. Is there another way to do multiple grouping ? Every thing is working fine except for getting one extra blank page at the end of report with only header and footer. I have properly checked the body height and width and page margins. It has something to do with list but i cant figure out what.

View 1 Replies View Related

Permissions With Windows Groups And View To Other Databases

Aug 24, 2007

I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.

In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?

I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.

Thanks
Rob

View 4 Replies View Related

Transact SQL :: How To Display Two Groups Of Data Across The Top

Nov 10, 2015

I have a Cost table with the following field:

Year, Quarter, Cost1, Cost2

I would like to write a SQL so that it will give me something like that, so that I will have 2 years across the top with the same same columns.

2016 
 
2015

Quarter
Cost1
Cost2
 
Cost1
Cost2

1
100
200

[code]....

View 3 Replies View Related

Transact SQL :: How To Sum Sales Value Between Groups Of Values

Jul 3, 2015

I have a table 

Acct No         Sales
1                    90
2                   129
3                  1200
4                 270

The table in real life has much more data and more columns. I want to be able to select Acct No and sum of sales for each band

Bands: <250  //  250-500  // 500-1000 // 1000+

I want to be able to get a table as below:

Account No //  <250  //  250-500  // 500-1000 // 1000+
 1              //    90     //         0     //          0      //      0
 2              //    129   //         0    / /           0     //       0
 2              //    0      //         0    / /           0     //       1200
 2              //    0     //         270    / /           0     //       0

Is there any easy way of doing this?

View 4 Replies View Related

Transact SQL :: Find The Number Which Exists In Different Groups

May 25, 2015

I have a table which has 2 columns and the data is like below

API_Number        Group_Name

1234                     Group A
3241                     Group A
1234                     Group B
4567                     Group C
7896                     Group D
3241                     Group E

 I wanted to find the API numbers which are repeating in different groups. In the output I want

 API_Number           Group_Name

1234                       Group A,Group B
3241                       Group A,Group E

View 5 Replies View Related

Split Data File Into Multiple File Groups..!

Dec 18, 2006

I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....

View 1 Replies View Related

Transact SQL :: Optimize Count Distinct For Multiple Groups Of Records?

May 21, 2015

I have a CTE returning a recordset which contains a column SRC.  SRC is a number which I use later to get counts and sums for the records in a distinct list. 

declare@startdate date = '2014-04-01'
declare@enddate date = '2014-05-01'
; with SM as
(
SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row,
u.SRC,

[Code] ....

-- If Referral start date is between our requested dates

ref.Referral_Start_Date between @startdate and @enddate

OR

-- Include referrals which started before our requested date, but are still active during our date range.

(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL ))
)
INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id
AND s.Service_Delivery_Unit_Id = 200
)
SELECT
count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,

[code]....

View 5 Replies View Related

For Each Across List Of Databases

Oct 20, 2006

I need to get records from multiple databases.

In my main database, I have a list of databases related to seperate business units.

For each of those databases, I need to get a list of values from a table (the table exists in each database).

Basically

foreach database in a list
Do a Lookup
end

Possible?

View 4 Replies View Related

List Of Databases

Aug 21, 2006

hello anybody now

i need to find how many Databases in my local SQL server (programatically).i know i have 8 databases is there but i need to print each and every database.

can anybody help me.

thanx

kiran.

View 6 Replies View Related

List Groups That Have Access To Application And Use Grid Format To Show Access To Specific Tables

Jun 23, 2014

i am currently working on designing a database for a bank as a school project for my database class. We have to draw up an entity relationship diagram, Sql tables, database size estimate etc. I am currently working on the security portion of the project. I need to list the groups that have access to my application and use a grid format to show access to specific tables.

I am currently working on designing a database for a bank as a school project for my database class. We have to draw up an entity relationship diagram, Sql tables, database size estimate etc. I am currently working on the security portion of the project. I need to list the groups that have access to my application and use a grid format to show access to specific tables.

Role Loans Payments Transactions Accounts Customer Emplo
Database Admin SUID SUID SUID SUID SUID SUID
Branch Manager SUI SUI SUI SUI SUI SUI
Internal Auditor S S S S S S
Loan Officer SUID SUI SUI S S
Tellers S S S S SU
Customers U

View 1 Replies View Related

Query A List Of Databases

Oct 23, 2006

anyone know how to query a list of databases?

current the sp_helpdb seems cant help me, because it query all the details, i want to do it like select * from database where database id=2

View 5 Replies View Related

List Databases With Their Size

Feb 12, 2007

I want to list my databases, and physical related files, and their size but the following commands do not provide the size. Any idea?

SELECT * FROM sysdatabases
SELECT * FROM sys.sysfiles


I know it's silly question

Canada DBA

View 6 Replies View Related

List Attached Databases?

Mar 12, 2007

Is there a stored procedure to list attached databases?
You can do this:
use Master
exec sp_helpfile
go

Which shows you the config files for the current DB.
What if you want to do a multiple sp_detach_db? If you could get a list of the attached DB's you could recursilvely or conditioinaly detach.

View 4 Replies View Related

List Of Attached Databases

Feb 7, 2006

How can I, programatically, get a list of attached databases? I'm using VB.

View 4 Replies View Related

Getting List Of Databases From Server

Sep 25, 2006



Is there a way I can query the database server (SQL 2000) to get a list of all the available databases? As a threshold problem I am not clear how I would connect to the server. I am using VB.Net and presently connect to one database at a time. The connection string is in an XML file, such as:

<?xml version="1.0" encoding="utf-8" ?>

<Connection>workstation id=ASUS;packet size=4096;user id=sa;data source="5.15.183.222KWMSDE2"; persist security info=True;initial catalog=tsLocal;password=pass

</Connection>

Is there some special way that you connect to the server without specifying the database?

Is there a certain query I must use once I am connected? Can the information be returned in the form of a data table in a data set?

Thanks.

View 4 Replies View Related

SAN&#39;s And File Groups

Jan 8, 2001

If you have a SAN, is there any real benefit to breaking out large tables into file groups?

View 1 Replies View Related

File Groups

Nov 3, 2007

I am looking to find out when to use file groups when backing up. When should you use this, what's the benefit over just doing a full db backup? Is it better when you are dealing with large db's?

Also this question has been on my mind for a while. Why shouldn't you shrink the db after every full backup? What is the negative in doing so?

Thanks

View 1 Replies View Related

File Groups

Jun 28, 2006

Hi everyone,
When I do the following, did I put the files in Test1FG1 file group to the default file group(Primary) ?

ALTER DATABASE Test1

MODIFY FILEGROUP Test1FG1 DEFAULT
GO

Thanks

View 8 Replies View Related

File Groups

Jun 26, 2006

Hi everyone,
While creating our database in only one disc(C or D), suppose that we create more than one file group in order to group our data files. However, in this situation; I wonder that whether it brings any benefit or advantage to us.

Also, I wonder that why we always have to put our data file into separate file group if we use separate discs for data files. Is not it allowed to use only one file group even if we use separate dics ?

Would you explain these to me ?

Thanks

View 13 Replies View Related

List Of All Table Sizes In A Databases

Mar 6, 2000

Hi,

Is there a query I can run to retrieve a list of all tables and their sizes in a database? I want something that is like the feature in Enterprise Manager when you click on a database and then the 'Tables & Index' link. It lists the tables and their respective size. I want to push this into a spread sheet.

The reason why I am doing this is the compare data between 2 different databases. Since I cannot find a tool that will compare the data, the closest I can get (without bcp-ing out all data and comparing) is to look at the sizes of each table.

Thanks!
Joyce

View 2 Replies View Related

Browse Through A List Of Databases On A Given Server

Nov 11, 2004

Hi,

I would like to allow the user of my ADO.NET application to browse through a list of the available databases on a given server. INFORMATION_SCHEMA in SQL Server Books Online doesn't describe how to do this. How can I do this?
TIA,
Royce

View 2 Replies View Related

List All Databases That A User Has Access To

Nov 17, 2014

Any way to list all databases that a user has access to?

View 6 Replies View Related

List Of Databases On An Instance Of SQL Server

Mar 28, 2007

Is there a way of limiting the "Databases list" to just the databases which I have permission to?
My databases is in a hosted environment, and the list of databases on that server is HUGE.

View 1 Replies View Related

List Of Databases In Analysis Services

Jul 23, 2005

Hi all,I have to write a program to read the names of databases in AnalysisServices. I don't know which table I can get this information from.Thanks a bunch

View 2 Replies View Related

Want To Grab A List Of Available Databases On A Given Server

Jul 23, 2005

I have MSDE running on my system and I want to get a list of databasesfor that server programmatically. I'm using vb.net and I want to view,add, delete and modify databases on a server from within a class.I'm aware I can use the "Server Explorer" feature in Visual Studio, butthat isn't what I'm looking for. Any references or suggestions on whereto start are appreciated.TIARalf

View 7 Replies View Related

List Of Columns From Tables Across Databases.

Jul 23, 2005

Hey guys,Couldn't find this anywhere in google.I want a list of all database column names for a specific table/viewfrom across database.I tried this...-----------------------------------------------------Select *[color=blue]>From Information_Schema.Columns[/color]-----------------------------------------------------I also tried this...-----------------------------------------------------select syscolumns.name, sysobjects.name, * from syscolumns, sysobjectswheresysobjects.id = syscolumns.idand (sysobjects.xtype='U' or sysobjects.xtype='S')-----------------------------------------------------These queries return information about the CURRENT database.But, if I want to do it ACROSS database or across servers.. how can Ido this?I will express my gratitude to everyone who is kind enough to answerthis question. (I've been stuck with this problem for a while now.)Thanks!OhMyGaw!

View 6 Replies View Related

List Of Databases From Multiple Servers Without SMO

Nov 14, 2007



Is it possible to get an aggregate list of databases from multiple servers, without using SMO?

All target servers are running SQL Server 2005.

Ben Aminnia

View 2 Replies View Related

List All Databases That A User Has A Login

Nov 26, 2007

Hi everyone,
I have an instance with many databases in it. I am looking for the easiest way to see which of those databases a user has a login on. What is the most efficient way of doing this?
Thanks,
Anil

View 5 Replies View Related

Reduce File Groups

Aug 17, 2000

I had a database that’s comprised of different file groups and log files spread out among different hard drives. I have recently upgraded the database to SQL 7.0 on a RAID 10 volume. I would like to consolidate all the file groups and files as well as various log files into one primary datafile and logfile. How do I do that? Thanks in advance.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved