Query For Determining Which Partition Belongs To Which Filegroup?
Oct 13, 2006
Dear guys,
Is there a way to determine which partition belongs to which Filegroup name?
Nabeel
ADVERTISEMENT
Find How Much Of Partition Filegroup Contains Data?
Oct 7, 2015I have partitions that I have filled with data. I am not trying to figure out exactly how much data the partitions contain, and therefore I will be able to see if any of them are close to hitting their autogrow conditions. If I were looking at a single unpartitioned table, then I could maybe look at the table properties to determine data and index sizes, and compare that to the size of the mdf file size, but for partitions, then I am not sure how I would query this information out. Any pointers on how this information could be queried out of the system?
View 3 Replies View RelatedSQL 2012 :: How To Add New Filegroup To Existing Partition Scheme
Jul 10, 2014How to add some more ranges to existing partition schema and function?
Already My table partitioned on date ranges,
6 partitions , each partition contains 6 moths data, so total data is 3 years.
i.e. 1 partition data- from jan2012 to Jun2012
2 partition data- from july2012 to dec2012
3 partition data- from jan2013 to Jun2013
4 partition data- from july2013 to dec2013
5 partition data- from jan2014 to Jun2014
6 partition data- from july2014 to dec2014
After Jan2015 data will go to Primary file group(Default)
Now customer wants to add two more file groups with these partitions ranges, i.e. jan2015 to jun15 and Jul15 to dec15.
File group and ndf file adding is OK, But
how to alter partition scheme and partition function with these additional ranges to existing partition function and scheme?
partitioned table size is 200 GB.
How To Change An Existing Table From A Filegroup To A Partition
May 21, 2008I've create a partition function and a partion scheme for my database.
Now I would like to change an existing table to use these partition.
The table is replicated.
How can I do this?
Thanks
Markus
SQL Server Admin 2014 :: Adding New Partition To Filegroup
Nov 28, 2014I work for a 24/7 shop. We currently have a table that is partition on monthly. I have to created a script that will add a new file group, add the new file to the group, and alter the the partition scheme and function. However, I need for this process to not cause a lock on the table. Typically I get the locking and issues when I am run the split command. Is there a way to prevent this from happening?
View 4 Replies View RelatedT-SQL (SS2K8) :: Use Variable In ALTER / CREATE PARTITION SCHEME Command For Filegroup?
May 9, 2010Is it possible to use a variable to specify the filegroup in the ALTER/CREATE PARTITION SCHEME command?
I want the partition scheme to use the default filegroup for ALTER and CREATE PARTITION SCHEME. At the time the script is created, I don't know the default filegroup in the database.
My code:
declare @fileGroupName VARCHAR(50) = (select top 1 name from
sys.filegroups where is_default = 1)
ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName
Is failing:
Incorrect syntax near '@fileGroupName'.
Q: Is it possible to use a variable for the filegroup in the ALTER/CREATE commands? Is so, what is the correct syntax?
Q: If using a variable is not possible, is there another way to specify the default filegroup?
Transact SQL :: Query To Retrieve To What Database A Table Belongs
Jul 16, 2015I need a query to list the tables in SQL sever with respective database they belong across databases.
I can obtain list of tables from Sys.Tables / Sys.Objects Views but how do I correlate to which DB a table belongs to?
The Sys.Databases View list the databases with their respective Ids. But, the DBID is not part of Sys.Tables / Sys.Objects Views.
Which View will allow me to fetch database to table mappings?
Determining If SQL Statement Is A Query
Jul 27, 2007Hi,
I have a C# server application which clients can send arbitrary SQL statements to. These can be absolutely anything - creating tables/views, selecting from tables/views, inserts, updates, deletes, you name it.
There are two return parameters from the server method which executes the SQL - a results set containing the data, and a count of the rows that were updated - (either one or the other should be populated depending on the type of command sent to it). To deal with this, what I planned on doing was (pseudocode follows..):
try
{
reader = OpenReaderCursor(statement)
}
catch
{
updatedCount = ExecuteNonQueryCommand(statement)
}
Unfortunately this doesn't really work, as OpenReaderCursor is able to execute Non Queries (eg. UPDATE/INSERT/DELETEs etc) but doesn't give me a row count, and trying the other way round, ExecuteNonQueryCommand is happy to execute SELECT statements, but I can't then of course return a results set as I don't have access to it.
My question then, if you will excuse the waffle above, is "Is there a simple way of determining if a string containing an SQL statement is a query?" - or will I have to come up with some way of dealing with this in my application code?
Please don't slate the design (ideally I would have two methods on the server, one for queries which returns results and the other for nonqueries which returns an updatecount) but there's nothing I can do, this is how it must be done (the interface was defined long long ago)
Thanks for your time, all ideas appreciated
Jim
Nested Query Or Should I Use Over Partition Perhaps?
Nov 7, 2007I have the following result set #1 from the query below. As you can see, there are four different provider roles and four different physicians listed for the same case (acctnum). Is there a way I could add a nested select (or other method) which would allow me to list this case as one line item to appear in the manner of result set #2?
RESULT SET #1 (the results of the query I have now)
MRN
ACCTNUM
PTNAME
AGE
ADMDT
DISCHDT
LOS
PROVIDER_CODE
PROVIDER_ROLE
PHYSNAME
12345
11111117777
DOE, JANE
48
Nov 29 2006
Nov 30 2006
1
10
ANE1
MILLER DR.
12345
11111117777
DOE, JANE
48
Nov 29 2006
Nov 30 2006
1
20
ADM
MAY DR.
12345
11111117777
DOE, JANE
48
Nov 29 2006
Nov 30 2006
1
30
ATT
SCHULTZ DR.
12345
11111117777
DOE, JANE
48
Nov 29 2006
Nov 30 2006
1
35
PRIN
THOMAS DR.
RESULT SET #2 (this is how I desire the results to look)
MRN
ACCTNUM
PTNAME
AGE
ADMDT
DISCHDT
LOS
ANE1PHYS
ADMPHYS
ATTPHYS
PRINPHYS
12345
11111117777
DOE, JANE
48
Dec 13 2006
Dec 14 2006
1
MILLER DR.
MAY DR.
SCHULTZ DR.
THOMAS DR.
Select
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(pt.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(pt.patient_sname)), '')
AS SRM_PatientName,
pm.PatientAge,
left(e.admission_date,11) as Admit_Date,
left(e.episode_date,11) as Disch_Date,
(CASE WHEN DATEDIFF(DAY, e.admission_date,e.episode_date) = 0 Then 1
ELSE DATEDIFF(DAY, e.admission_date,e.episode_date) END) AS LOS,
epi.PROVIDER_CODE,
epi.PROVIDER_ROLE,
pe.PERSON_NAME as physician_name,
From srm.episodes e inner join
dbo.PtMstr pm on pm.accountnumber=e.account_number inner join
srm.ITEM_HEADER ih ON ih.ITEM_KEY = e.EPISODE_KEY INNER JOIN
srm.PATIENTS pt ON pt.PATIENT_KEY = ih.LOGICAL_PARENT_KEY inner join
srm.CDMAB_PROV_EPI epi on epi.episode_key=e.episode_key inner join
srm.providers p on p.provider_key = epi.provider_key inner join
srm.person_element pe on pe.item_key = p.provider_key
Where e.episode_date is not null and pm.AnyProc like '%4495%'
Add Other Table Fields To An Existing ROW_NUMBER Partition By Select Query
Feb 5, 2008
I have the following insert query which works great. The purpose of this query was to flatten out the Diagnosis codes (ex: SecDx1, SecDx2, etc.) [DX_Code field] in a table.
Code Snippet
INSERT INTO reports.Cardiology_Age55_Gender_ACUTEMI_ICD9
SELECT
Episode_Key,
SecDX1 = [1],
SecDX2 = [2],
SecDX3 = [3],
SecDX4 = [4],
SecDX5 = [5],
SecDX6 = [6],
SecDX7 = [7],
SecDX8 = [8],
SecDX9 = [9],
SecDX10 = [10],
SecDX11 = [11],
SecDX12 = [12],
SecDX13 = [13],
SecDX14 = [14],
SecDX15 = [15]
FROM (SELECT
Episode_Key, DX_Key,
ROW_NUMBER() OVER ( PARTITION BY Episode_Key ORDER BY DX_Key ) AS 'RowNumber', DX_Code
FROM srm.cdmab_dx_other
WHERE Episode_key is not null
) data
PIVOT
( max( DX_Code ) FOR RowNumber IN ( [1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12], [13], [14], [15] )) pvt
ORDER BY Episode_Key
The query below also works fine by itself. You may notice that the Episode_Key field appears in both the query above and below therefore providing a primary key / foreign key relationship. The srm.cdmab_dx_other table also appears in both queries. I would like to add the fields in the select statement below to the select statement above. Using the relationships in my FROM statements, can anyone help me figure this one out?
Code Snippet
SELECT
e.episode_key,
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(p.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(p.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(p.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(p.patient_sname)), '') AS PatientName,
CONVERT(CHAR(50), e.admission_date, 112) as Admit_Date,
CONVERT(CHAR(50), e.episode_date, 112) as Disch_Date,
e.episode_type as VisitTypeCode,
d.VisitTypeName,
convert(int, pm.PatientAge) as PatientAge,
pm.PatientAgeGroup,
pm.patientsex,
p.race
FROM srm.episodes e inner join
srm.cdmab_dx_other dxo on dxo.episode_key=e.episode_key inner join
srm.cdmab_base_info cbi on cbi.episode_key=e.episode_key inner join
srm.item_header ih on ih.item_key = e.episode_key inner join
srm.patients p on p.patient_key = ih.logical_parent_key inner join
ampfm.dct_VisitType d on d.VisitTypeCode=e.episode_type inner join
dbo.PtMstr pm on pm.AccountNumber = e.Account_Number
Transact SQL :: Apply Partition Scheme To Existing Table In Query Windows
Jun 9, 2015I have a non-partitioned table (TableToPartition) and I want to apply an existing partition scheme (PartSch) to it using a query. I didn't find any option so I used the StorageCreate Partition wizard to generate the script.why this clustering magic needed if it is dropped at the end? Isn't there another way without indexing to partition a table, say something with ALTER TABLE? (SQL Server 2012)
BEGIN TRANSACTION
CREATE CLUSTERED INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
(
[ID]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartSch]([ID])
DROP INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
COMMIT TRANSACTION
This Row Already Belongs To Another Table.
Feb 21, 2007Hey,
I have a function that executes a query and fill the result in a dataset then return the row as following :
Public Function get_item_row(ByVal query, ByVal ordernum, ByVal theitemid, ByVal center) As DataRow
Dim conn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("conn"))
Dim adbooks As New SqlDataAdapter
Dim Str As String
Dim result As New DataSet
Dim MyDataRow As DataRow
adbooks.SelectCommand = New SqlCommand
adbooks.SelectCommand.Connection = conn
Str = "select * from " & query & " where ordernum=" & ordernum & " and theitemid=" & theitemid & " and center='" & center & "'"
adbooks.SelectCommand.CommandText = Str
adbooks.Fill(result)
If (result.Tables(0).Rows.Count > 0) Then
MyDataRow = result.Tables(0).Rows(0)
Else
MyDataRow = Nothing
End If
Return MyDataRow
End Function
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
then i am calling that function as following:
Dim MyRow As DataRow
MyRow = b_list.get_item_row(query, theorder_b, theitemid_b, center)
then i am adding that row to a datatable:
Dim updateTable As New DataTable
updateTable.Rows.Add(MyRow)
and then i am gettin the error!
plz help
thx
Hiba
How To Find Out Which NT Group(s) An User Belongs To?
Apr 14, 2004How to find out which NT group(s) an user belongs to? any ideas?
View 4 Replies View RelatedHow2identify, Which Table Does The Object Belongs
Apr 16, 2008sorry for short subject, it allows very less characters, seems like varchar(50)
Hi,
My questions is...
How to identify an object using its objectid. In profiler and activity monitor we see objects like
1:12345
1:2345:453454
and so on...
I am sure they are not tables or indexes and I guess they are something related to pages, rowids etc.,
how do i interpret these objects (ids) and point back to the table (if it belongs to any table)
to summarize what I want
how to identify the type of object is being shown in profiler/activity monitor
how to identify to which table/index that object belongs/associated
where can i get object name for any object id that is shown in profiler/activity monitor. some time it shows some object ids in 100s of thousands for which i don't see any entry in sysobjects/sysindexes
Thanks in advance
AdomdClient: Which Sequence Belongs To Which Cluster?
May 17, 2007Hi,
We are using Microsoft Sequence Clustering both "manually" via Visual Studio (SQL Server Business Intelligence Development Studio) and "programatically" via AMO and ADOMD.NET (using C#). We use AMO to build and process the MiningModel, and ADOMD.NET (Microsoft.AnalysisServices.AdomdClient) to browse through results.
Everything is working fine except for a very important detail: we can't find where the MiningModel stores cluster membership. The MiningModel.Content contains a column NODE_SUPPORT which shows how many sequences there are in each cluster, but we can't find which sequence belongs to which cluster.
In Visual Studio, the Microsoft Sequence Cluster Viewer is able to show this in Cluster Profiles (a right-click on a cluster followed by Show Legend lists the sequences that belong to that cluster).
Question is: where does the Viewer get this information, and how can we get it via ADOMD? Or via AMO, if that's the case?
Would appreciate your assistance.
Kind Regards,
Diogo
When Creating, How To Specify That Table Belongs To A Particular Schema?
Mar 9, 2006Using Management Studio, when i create a new table/stored proc., the owner is always dbo. How do I change this when I create it? Also how to transfer it to another schema once it has been created?
Check Which Group User Belongs To
Jul 15, 2015we do get incidents saying user can't login even adding to the group.So is there any script to check which group the user xxxxx belongs to from SSMS?
View 2 Replies View RelatedCheck If A MemberName Belongs To DB_Owner Role
Feb 29, 2008Hi guys,
I'm trying to write a script that will check is a MemberName belongs to the Db_Owner role and if it doesn't I then want to add that membername to the role. Does anyone know how to do this?
I have managed to list the Db_Owner MemberNames by using the sp_helprolemember stored procedure, and I can use the following:
SP_addrolemember 'db_owner' , 'ASPNET'
to add the membername to the db_owner role, it's just conditional only do it if it doesn't exist that is causing me grief.
Many thanks,
Damien
How To Xfer Db_owner From User Back To Where It Belongs?
Aug 22, 2007OK. I screwed-up. SS2005. I gave a user ownership of schema "db_owner" and now I can not change it back to what it was (unknown) so I can delete the user. So how do I change ownership of "db_owner" back to what it was so I can delete the user???
Thanks,
Michael
SQL Server 2008 :: How To Find Which Backup File Belongs To Which LSN
Mar 18, 2015I am getting following error:
"The log in this backup set terminates at LSN 9566000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 986000002731000001 can be restored."
If I am missing to restore previous log backup file, how to find which LSN mapped to which log backup file (.trn file name)? Another word looking at LSN can we tell which log backup file is missing?
Transact SQL :: Select Value Only If It Belongs To One Primary Key Otherwise Return Null
Jul 18, 2015Using TSQL, I have a table that holds filenames of Pictures for products. Different products can be using the same picture. I need to select the filenames for a single product only if it does not exists for a different product.I have tried Where Exists (select FileName From Tbl where
Prod_Id = @var) AND NOT EXISTS(select FileName From Tbl where
Prod_Id != @var) In the Select Statement.
Query Server To Find All Partitioned Tables, Partition Name, Column Used, Partitioned By
Dec 17, 2007I want to find a way to get partition info for all the tables in all the databases for a server. Showing database name, table name, schema name, partition by (maybe; year, month, day, number, alpha), column used in partition, current active partition, last partition (for date partitions I want to know if the partition goes untill 2007, so I can add 2008)
all I've come up with so far is:
Code Block
SELECT distinct o.name From sys.partitions p
inner join sys.objects o on (o.object_id = p.object_id)
where o.type_desc = 'USER_TABLE'
and p.partition_number > 1
Need Efficient Query To Partition Records By Type And Pull Top N Records From DB
Jan 18, 2008I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.
;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
Thanks in advance.
Determining If A Server Is 32 Bit Vs 64
Jun 11, 2007
Is there a quick and easy way to figure out if a server is 64 bit or 32 bit? I have been looking and cannot figure out an easy way. If there is a script that will figure it out could you please tell me. I am a DBA and manage over 100 servers and need a fast and easy way to figure this out. I need to know this to pick the correct upgrade version.
-Kyle
Determining If A Server Is 32 Bit Vs 64
Jun 11, 2007Is there a quick and easy way to figure out if a server is 64 bit or 32 bit? I have been looking and cannot figure out an easy way. If there is a script that will figure it out could you please tell me. I am a DBA and manage over 100 servers and need a fast and easy way to figure this out.
-Kyle
SqlDatareader Belongs To Which Class Is It Sealed Or Static Class?
Feb 21, 2008Hai Guys,
I have a doubt Regarding SqlDataReader
i can able to create object to Sqlconnection,Sqlcomand etc...
but i am unable to create object for SqlDataReader ?
Logically i understand that SqldataReader a way of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited.
sqlDatareader belongs to which class is it sealed or static class?
can we create own class like SqldataReader .......
Reply Me ...... if any one know the answer..............
Determining What To Input As Server
Jun 9, 2006I'm trying to connect to a sql database, but I don't know what myserver is in the following code.Dim strConn As String = "server=myserver;database=Northwind"I can't get the code to link up with my Northwind database.I'm running everything locally if that helps.Thanks!Jon
View 2 Replies View RelatedDetermining Database Size
Oct 31, 2001Is there a system stored procedure that I can execute that will return the actual size of the database you are working with? Any information is appreciated.
View 1 Replies View RelatedDetermining If A Table Exists
May 2, 2000How do I find out if a temporary table named '##test' exists? I have a stored
procedure that creates this table and if it exists another stored procedure
should do one thing, if it does not exist I want the SP to do something else.
Any help as to how I can determine if this table exists at the current time
would be greatly appreciated.
Thanks in advance for you help,
Jon
Determining DB Type/Character Set
Feb 26, 2002Can anyone tell me what command/utility i can use to determine the database type (non-unicode or unicode) as well as the supported character set?
Any help will be greatly appreciated. Thanks a lot!!
Determining Last DBCC Procedure
Mar 7, 1999Hi,
Is it possible to determine when last a DBCC statement was run on a database & what that DBCC was.
TIA
Shaun Tinline
Database Administrator
Alcatel Altech Telecoms
Determining Unique Children
Nov 17, 2005Hi,
I need to be able to identify if a parent has the same child names as another parent.
Have a look at my data (in pic)
Can you think of a SELECT that will count for each parent, the number of other parents including themselves with all the same children's names?
So you would get something like:
Bob | 2
Bill | 1
Glen | 2
thanks,
david.
Determining Database Size
May 9, 2008Forgive the easy question but I'm afraid it might be also a trick question and I'd like to hear the experts' opinion. I am using SQL Server 2005 Express edition and I know the limitation is 4GB per database. So far none of my users is anywhere near the limit but I have to be prepared for when that day finally comes. As it stands, they use a single database through a program so I have full control over it. There are no fancy backup programs on the system so no fancy recovery models and automatic shrinking can be done - data is only inserted in that database.
My question is simply how can I determine programmatically (I use ADO.Net but it can execute SQL commands just fine) the size of the database as it relates to the limitation? That is, I don't know whether it is the amount of data stored - with or without overhead, or it is simply the size of the *.mdf file (maybe together with the *.ldf file), or whether the 4GB is 4 billion bytes or 2^32 bytes - I just want the same method that the SQL Server is using so that, for example, I can bring up a warning at 90% full and lock out the user at 99% full.
I'd appreciate any insights.
Kamen