SQL 2012 :: Way To Remove All Indexes From A Table With One Single Command

Sep 29, 2015

IS there a way to remove all indexes from a table with one single command ( without having to know what the index name is ) ;

View 2 Replies


ADVERTISEMENT

Grant Permission On More Than One Table In A Single Command

Aug 24, 2000

Hi

Does anyone know how i can grant select permission on more than one table in the same database using a single grant commmand....its painful to use grant statement seperately on each table

thanks and regards,
reshma

View 3 Replies View Related

SQL Server 2012 :: Why Indexes On Table Slow Down DML Operation On Table

Mar 7, 2014

Why the Indexes on table slow down the DML operation on table, what is the exact reason?

View 5 Replies View Related

SQL 2012 :: Identify Unused Indexes In Table

Jan 15, 2015

How to find if there is a query that can be written on DMV's which will be able to retrieve the indexes that are not being used in a table.

View 2 Replies View Related

SQL 2012 :: Remove Digits From Fields Of Table

Apr 3, 2014

I have table like

name
22rajuvar
45 vamsgui
87 praveen
67kumar
32 vamshi

Above field mix with digits and character. I want to remove digits. I want o/p like ............

name
rajuvar
vamsgui
praveen
kumar
vamshi

View 8 Replies View Related

SQL 2012 :: Script To Remove Any Foreign Characters From Table

Sep 14, 2015

I have a table that is riddled with weird characters. So far I have found an escape character for PDF files and a trademark sign. These characters are crashing my SSIS packages. I am able to remove these characters with an update script...

Update TABLE
set LEAD_NOTES__C = Replace(LEAD_NOTES__C, nchar(65533) COLLATE Latin1_General_BIN2, '!');

Update TABLE
set LEAD_NOTES__C = Replace(LEAD_NOTES__C, nchar(1671) COLLATE Latin1_General_BIN2, '!');

This works fine, but my question is...

I would like to write a script that removes all foreign characters with the exception of the normal characters like (@,#,$,%,etc). I need a dynamic process that handles this so I am not losing time sifting through over 20,000 rows of data and changing my update script to remove a specific column. Although this method works, I would prefer a dynamic query. I intend to wrap this in a stored procedure that loops through all columns in a table (as parameter).

View 4 Replies View Related

SQL 2012 :: Performance Limit On Number Of Indexes Per Table / Database

Oct 1, 2014

Is there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.

View 4 Replies View Related

SQL 2012 :: SSIS Multi Configuration In A Single Table

Oct 20, 2015

I want to maintain all configurations in Single table, what is the best way to approach it.

View 5 Replies View Related

SQL Server 2012 :: Stored Procedure - How To Return A Single Table

Nov 21, 2013

I have this SP

ALTER PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int)
AS
DECLARE @steps bigint
SET @steps = DATEDIFF(hour, @start, @stop)/ @step
DECLARE @i bigint
SET @i=0

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Merge Multiple Rows From Single Table

Nov 17, 2014

I have resulting rows from a query similar to the following:

The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:

SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,

[Code] ....

I basically want to merge the like Employee ID's together into a single row like the following:

I know I have done this before and it is probably pretty simple.

View 4 Replies View Related

SQL Server 2012 :: How To Add Column To Multiple Table Using Single Script

Feb 12, 2015

I am looking a script which allow me add single coilumn to multiple table of my database.

For Example :-

I am having 4 table

1-Emp , 2-Dept , 3-Location , 4-Salary like this I have around 100 of table

Now I want to run below command to add column Rowchecksum in all table where table name start with Archivebbx keywords.

Alter table EMP
Add Rowchecksum varbinary(8000)

View 1 Replies View Related

SQL 2012 :: Check Command That Ensures Only Certain Values Inserted In Table?

Jan 14, 2015

I am trying to create a check command that ensures only A'B','c','D','E','F','G and s1, s2 can be inserted in the table, is this even applicable? Heres my code:

Create Table GRADE (
GradeVARCHAR2(1) CONSTRAINT pk_Grade PRIMARY KEY
CONSTRAINT check_grade
CHECK (substr(Grade = 'A','B','c','D','E','F','G')),

Salary_Scale VARCHAR2(2) CONSTRAINT check_SScale
CHECK (substr(Salary_Scale = 'S1', 'S2')),
)
/

View 1 Replies View Related

Plz Help...update Value In Multiple Db Table Using Single 'update Command'

Mar 18, 2005

hi,friends

we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------

i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.

i want to update value in multiple database table(more than one) using single 'update command'

i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.

it's urgent.

thanks in advance.

View 2 Replies View Related

SQL Server 2012 :: Insert Values In A Single Table With Four Columns From 4 Different Sources?

Jan 30, 2014

I am trying to insert values in a single table with four columns from 4 different sources. is it possible to run these 4 insertions in parallel. all these insertion are independent of each other

View 3 Replies View Related

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.ie.

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 Replies View Related

SQL 2012 :: Single Column In Staging Table Deems Retrieval Query Unresponsive

Jul 17, 2015

where including/excluding a single column in an empty staging table would influence a resultset returning from distributed query? Both servers are SQL Server 2012. Nothing special about the staging table. It contains 12 columns with a mixture of INT and NVARCHAR(256) columns. In one case I exclude the column and the query returns in 17 seconds. When I include it the query does not return. Excluding the INSERT INTO the staging table and query returns in 17 secs with and without the column.

View 3 Replies View Related

Command For Rebuild And Reorganize Indexes In MSSQL Server2000

Mar 25, 2008

Hi,
I have a script to rebuild and reorganize indexes for sybase i.e reorg rebuild index... like command i have. Now i want similar command for MSSQLSqlserver.plz help me.

View 2 Replies View Related

SQL Server 2008 :: Single Script To Remove Secondary Log Files

Mar 18, 2015

I am new to SQL and I haven't written any scripts in the past. I thought I would give it a go. Basically, I am trying to write a script that will check if a database has more than one log files, free the VLFs that belong to the secondary log files and then remove them. I created a database named rDb as this link suggests and followed the steps.

[URL] ....

It works. However, I want to have to run just 1 script that will do the entire job. This is what I have gotten so far and it doesn't work:

create table #tempsysdatabase(
File_id int,
file_guid varchar(50),
type_desc varchar (20),
data_space_id int,
name nvarchar (50),
state int,

[Code] ....

View 0 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 12, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???





View 1 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 16, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???


thanks,

Ran Kizi

View 3 Replies View Related

Can Someone Clarify Why Only A Single-statement Can Be Executed In A Command?

Jun 21, 2006

I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions.

I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time?

For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition?

View 6 Replies View Related

Single Command To Change The Backup Device Path

Feb 29, 2008



Hi guys.

acutally my network dept. has change the backup file server IP address.. i m now hving problem for taking backup.
i have around 85 backup that runs everyday.. what i m doing now running every single command to drop backup device and then adding again.. but it's taking agess to do..

is there any simple script that just update the device path folder..

Thanks and looking foward.
-MALIK

View 6 Replies View Related

SQL 2012 :: Create New Indexes

May 24, 2014

1)When we create Indexes, key columns are the columns that use in where clause and included columns are the columns that can be used in the select list and on join clause column.

2) I am thinking that we have to create new Index, only if we found at least 50 msec time save.

View 9 Replies View Related

SQL 2012 :: Disadvantage Of Having Too Many Indexes

Aug 6, 2015

Any disadvantage of having too many indexes?

View 3 Replies View Related

Combine Data In Single Row From Single Table

Apr 4, 2006

How can i combine my data in single row ? All data are in a single table sorted as employeeno, date


Code:

Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000


Should Appear


Code:

EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800

PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.

THANKS IN ADVANCE

View 3 Replies View Related

SQL 2012 :: Can't Remove DB Mirroring

Oct 26, 2012

I created a mirrored DB, added a new datafile to the principal using a path the mirror can't access. As a result the mirroring session was suspended because the mirror stated: "CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file".

Fine, so I disabled mirroring on the principal (SET PARTNER OFF) which worked just fine, but the mirror stayed in a mirrored state, Object Explorer saying:"(Mirror, Disconnected / In Recovery). Sooo, I tried to disable db mirroring on the mirror using ALTER DATABASE db SET PARTNER OFF;, which completed successfully, but the DB STILL remained in a mirrored-configuration.

I tried ALTER DATABASE db SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS which resulted in

"Msg 1404, Level 16, State 4, Line 1

The command failed because the database mirror is busy. Reissue the command later."

I tried taking the DB offline or restoring it from a backup, but all these operations resulted in "The operation cannot be performed on database "db" because it is involved in a database mirroring session or an availability group."

The only solution I can think of at the moment is shutting down the instance and deleting the data and log-files of the mirrored db, which would be just fine because this is just a test installation, but it would be not quite as easy in a productive environment.

Is there any other way to remove the mirrored state from a disconnected mirror or to simply get rid of the db entirely to perform a recovery?

View 9 Replies View Related

SQL 2012 :: Database Remove From AG

May 19, 2015

As part of patching i need to remove the database from availability group and add the database after patching is done , how can i achieve this ?

View 2 Replies View Related

SQL 2012 :: Filtered Indexes In Server

Aug 14, 2014

What are driving criteria for creating filtered indexes on SQL server. I am trying to analyze the index stats through DMV,histogram and have to analyze if the filtered indexes should be created on tables. This exercise has to be done for all the transaction tables on the database. What are the approaches I should be looking on?

There was a deadlock on the DB because of huge writes on one of the big tables. Having filtered index on this table for the effected column would reduce the time taken for write operations. Hence we are looking for creating filtered indexes appropriately

View 8 Replies View Related

SQL 2012 :: How Indexes Allocated On Pages

Nov 6, 2014

How Indexes are allocates on pages? And If a CREATE INDEX Statement Executed on a query Window, Query processor meets and executes these query. However it was meet, who decides to separate indexes onto pages? Storage Engine or Query Processor(Query Optimizer)? Does it work like UPDATE-Statements in Query Optimizer?

View 4 Replies View Related

SQL 2012 :: Remove TempDB NDF File

Jul 8, 2014

I have added ndf to tempdb for checking performance improvement.... Now I want to remove the ndf file... I am using below command...

USE tempdb
GO
DBCC SHRINKFILE (3, TRUNCATEONLY);
GO
use master
go
ALTER DATABASE TEMPDB Remove FILE tempdev1

Results:
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
23766476643232

Error:-
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 1

The file 'tempdev1' cannot be removed because it is not empty.

Note:
=>I restarted SQLServer from SSMS and then ran same commands mentioned above ,......and getting same error...
=> I executed above commands and restarted services...no change...

How to remove / drop ndf file...

View 7 Replies View Related

SQL Server 2012 :: Remove First And Last Char

Jun 5, 2015

How to remove first and Last character from below select query.

SELECT LTRIM(RTRIM(REPLACE(col_1,'Year_',''))),
FROM Years

View 3 Replies View Related

SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table

Jun 25, 2015

I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.

In order to do that, I have taken the records based on the fragmentation %.

But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.

create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),

[Code] ....

View 5 Replies View Related

SQL Server 2012 :: Indexes Based On A Query?

Aug 18, 2014

trying to create indexes on two tables:

SF_Affiliate_Customer
SF_Affiliate_Customer_Account

on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..

SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt]
FROM [dbo].[SF_Affiliate_Customer] C
WHERE
( [afflt_intrnl_cust_ind] = 'N'
AND [afflt_empl_ind] = 'N'
AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA')
)AND

[code].....

View 3 Replies View Related







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