Friday, September 6, 2013

[T-SQL] Reindex script alteration

[T-SQL] Reindex script alteration

Reindex script alteration

Posted: 05 Sep 2013 08:10 PM PDT

Morning! I have a script which reindex's\reorgs index's depending on their fragmentation. But, it doesnt take into account the schema name, so anything other than DBO and the script fails.I have no idea how to incorporate this into the script. Below is step 1 of the process, which reorganised indexs fragmented between 5 and 40%. Thanks in advance for the help.CREATE procedure [dbo].[DefragIndexStep1] as --Create temp table for list of indexsCREATE TABLE #IndexFrag( database_id int, object_ID int, index_id int, name ntext, avg_fragmentation_In_Percent real )--Fill the table with all the indexs and fragmentation levelinsert into #IndexFrag (database_id, object_ID, index_id, name, avg_fragmentation_In_Percent)SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, b.name, ps.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id WHERE ps.database_id = DB_ID() ORDER BY ps.OBJECT_ID --select * from #IndexFrag--drop table #indexfrag--Selecting all index's between 5% and 40% fragmenteddeclare @cnt intdeclare @Result nvarchar(128)declare @cmd nvarchar(500) declare @tablename nvarchar(500)declare FindFragment cursor forSELECT name FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')--Cursor to go through each index which are between 5% and 40% fragmented and rebuildopen FindFragment fetch next from FindFragment into @resultwhile @@fetch_status = 0BEGINset @tablename = ( select OBJECT_NAME(object_id) FROM sys.indexes WHERE name = @Result) set @cmd = N'ALTER Index ' + @result + ' on ' + @tablename + ' REORGANIZE ' --print @cmd EXEC sp_executeSQL @cmd fetch next from FindFragment into @resultENDclose FindFragment deallocate FindFragmentdrop table #IndexFragGO

get value from a function in a Stored Procedure

Posted: 05 Sep 2013 09:56 PM PDT

Hi all,I was creating a stored procedure but in this sp for some columns I need to call a function and then assign that value to a column.here is procedure that I am writing:[code="sql"]select OFC.CompanyID, PRAD.ProjectID, PRAD.ProposalID, P.ProposalNo, P.ProposalName, PRAD.DateAdded, (P.Pri_Actual_TSP + PINFO.Pri_Actual_TSP) AS TSP, P.Pri_QSP + PINFO.Pri_QSP AS QSP, DiscountPercent -- for this column I need to call a function to get the value fn_GetPercentile ( Proposal.Pri_Actual_TSP + ProposalInformation.Pri_Actual_TSP, Proposal.Pri_QSP + ProposalInformation.Pri_QSPFROM ProposalRequestApprovalDetails PRADINNER JOIN ProcessProposalApprovalDetailsCLC PDC ON PDC.ProjectID = PRAD.ProjectID AND PDC.ProposalID = PRAD.ProposalIDINNER JOIN Proposal P ON P.ProjectId = PRAD.ProjectID AND P.ProposalId = PRAD.ProposalID AND P.OfficeId = PRAD.OfficeIDINNER JOIN ProposalInformation PInfo ON PINFO.OfficeId = P.OfficeId AND PINFO.ProjectID = P.ProjectId AND PINFO.ProposalId = P.ProposalId INNER JOIN vw_Office OFC ON OFC.OfficeID = PRAD.OfficeID[/code]How can i achieve this?

Query help to show a column

Posted: 05 Sep 2013 07:41 PM PDT

Please help on this:I want to show the value of ISNULL(TCBOV.cboValueName, '') AS PhaseType,even if the TCBOV.cboValueIncId and TCBOV.cboValueSqlId, are not present. I have tried to replace the INNER JOIN with LEFT JOIN for the CboValues TCBOV table, but still have not worked.SELECT S.studyCode AS studyCode, A.activityCode AS activityCode, ISNULL(TCBOV.cboValueName, '') AS PhaseType FROM Activities A WITH(NOLOCK) INNER JOIN TypesOfActivities TOA ON A.typeOfActivityIncId = TOA.typeOfActivityIncId AND A.typeOfActivitySqlId = TOA.typeOfActivitySqlId AND TOA.isDeleted=0x0 INNER JOIN ActivitiesCategories AC ON A.activityCategoryIncId = AC.activityCategoryIncId AND A.activityCategorySqlId = AC.activityCategorySqlId AND AC.isDeleted=0x0 INNER JOIN Studies S ON A.studyIncId = S.studyIncId AND A.studySqlId = S.studySqlId AND S.isDeleted=0x0 INNER JOIN TypesOfStudies TOS ON S.typeOfStudyIncId = TOS.typeOfStudyIncId AND S.typeOfStudySqlId = TOS.typeOfStudySqlId AND TOS.isDeleted=0x0 -- To Get PhaseType having the extract name "TrialFieldType"-- LEFT JOIN ActivitiesAdditionalFieldsValues AS TAFV WITH(NOLOCK) ON A.activityIncId=TAFV.activityIncId AND A.activitySqlId=TAFV.activitySqlId AND TAFV.isDeleted=0x0 INNER JOIN ActivitiesAdditionalFields AS TAF WITH(NOLOCK) ON TAFV.activityAdditionalFieldIncId=TAF.activityAdditionalFieldIncId AND TAFV.activityAdditionalFieldValueSqlId=TAF.activityAdditionalFieldSqlId AND TAF.isDeleted=0x0 AND (TAF.extractName = 'TrialFieldType') INNER JOIN CboValues TCBOV ON TCBOV.cboValueIncId = TAFV.cboRecordIncId AND TCBOV.cboValueSqlId = TAFV.cboRecordSqlId AND TCBOV.isDeleted=0x0

Help Required on the given scenario

Posted: 05 Sep 2013 09:19 PM PDT

I have a table called T1 and column called C1.It contains one code like TCR-ABCDE12345.I have another table T2 ,which has 4 columns and its values as shown below.C1 C2 C3 C4 ABCD 12345 PQRST 98765What I need to get from T1 ,when I query as below isSelect C1 from T1 Query Result : TCR-PQRST98765ie select SUBSTRING(C1,5,5) from T1 - Will be replaced with its corresponding value from T2 (ABCD will be replaced with PQRST)i.e.from fifth character till 10th of C1 of T1 needs to be replaced with its corresponding value C3 from T2select SUBSTRING(C1,10,len(C1)) from T1 Will be replaced with its corresponding value from T2 (12345 will be replaced with 98765)i.e.from 10 th character of C1 of T1 needs to be replaced with its corresponding value C4 from T2.

Query help

Posted: 05 Sep 2013 07:23 PM PDT

Hi friends,When I run the below procedure am getting as CPU utilization of the server is 185% ,300%. Where am I wrong in the script..create procedure CPUasbegindeclare @a as bigint, @b as bigint, @val floatselect @a=sum(cntr_value)from sys.dm_os_performance_counterswhere object_name = 'SQLServer:Resource Pool Stats' and cntr_type = 537003264select distinct @b=cntr_valuefrom sys.dm_os_performance_counterswhere object_name = 'SQLServer:Resource Pool Stats' and cntr_type = 1073939712set @val=(select cast(@a as float) / cast(@b as float)*100 as [cpu])print @valdeclare @temp varchar(max)set @temp='CPU utilization of server is '+CONVERT(VARCHAR(3),LEFT(@val,3))+'%'+' Check what is running in the server'if(@val>50)BEGINExec msdb.dbo.sp_send_dbmail @recipients='isdm@plintron.com',@subject='Server utilization is high',@body=@tempENDendThanks in advance.

How to generate data in lakhs format in SQL Server 2008

Posted: 05 Sep 2013 05:37 AM PDT

Hi, I have a issue where we got to report money column in string in the format 1,00,000.00Example..Input-100000.00 My required output is 1,00,000.00Input-10000.00 My required output is 10,000.00Can anyone please help me on this.Thanks in advance.

Working with Strings

Posted: 05 Sep 2013 04:19 AM PDT

From what I can find... it looks like working with strings is more of a headace .. in SQL than Pick. How do you test for a delimiter and then pull the string apart to show Code and Description?With the data, provided below.. I would be looking to show the following:Code Desc4925208 alcohol4921598 ethanol0196532001 WaxTable (keeping it simple):CREATE TABLE [dbo].[A_Test]( [Text_Code] [varchar](254) NOT NULL, CONSTRAINT [PK_A_Test] PRIMARY KEY CLUSTERED ( [Text_Code] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOData:insert into A_Test (Text_Code)values ('alcohol ~ 4925208'),('ethanol ~ 4921598'),('01965'),('Wax ~ 32001')
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog