Dynamically Drop Indices
Nov 22, 1999
Hi,
I thought I'd post this question again to see if I get a response this time. I am trying to figure out how to write a sp to drop all of a given tables indices. I have looked at the sp_helpindex text and see where the index name, index ID, table ID etc. is determined...no problem there. What I am having difficulty with is the actual DROP INDEX statement 'cause somehow I need to concatenate the table name to the index name and have the statement work...something like DROP INDEX @tablename + '.' + @indexname (which I can't get to work...sorry about my lack of SQL Srvr knowledge!). I was hoping someone might know if this can be done and how to do it.
Any help, even if it is to tell me this can't be done, would be greatly appreciated!!!
Thanks in Advance....
Kevin A
View 1 Replies
ADVERTISEMENT
Jul 31, 2015
We have a database we are replicating to about 8 SQL Express subscribers from a SQL 2012 SP2 publisher. The size of the database grew too large for the 10GB license limit for SQL Express and now replication refuses to replicate any of our deletions on the publisher to reduce the size of the database. I've come up with a few options below.
1) Drop one of the larger table indices on the subscriber database to get below the size restriction. Permit the replication to replicate the deleted records and then rebuild the index. (I'm not sure how important an index is to this table. Is it merely performance related?)
2) "Upsize" SQL Express to SQL Standard on the affected boxes. Allow the deletes to replicate. Backup the database, downgrade to SQL Express and restore the database back to SQL a new SQL express instance. This would involve a lot of work on each box. I'd like to avoid it if possible.
View 2 Replies
View Related
May 2, 2008
hi allCan somebody please explain to me what does it mean when someone saids use joint
indices to join tables?thanks in advance
View 3 Replies
View Related
Mar 26, 2002
I have created a new filegroup and would like to move my indices there. Do I need to create a file for each index or can I just specify the filegroup in Enterprise Manager?
Thanks!
Ellen
View 1 Replies
View Related
Nov 22, 1998
Recently, I assumed responsibility for a SQL Server 6.5 application( I am an Oracle DBA).
This application is being purchased from a vendor. It is replacing an Oracle application.
I did not make the decision for accepting this vendor's application
Yesterday, I had to call the vendor as I had a question. It turns out that the application
--with about 70 tables -- will not have any indices, only primary keys that will be utilized
through Powerbuilder. There will be no foreign keys as all such constraints are
handled through PB code.
I have dealt with many different applications over the years and under different platforms
but I have never encountered an application where there are no indices. I have seen
application with way too many and even some with too few. Never an application
without any.
Has any one ever worked with, or dealt with, an application with no indicies? Is it
practical to attempt to use native PB code to replace indicies? If yes, was the application
running efficiently? Was everything through table scans? Any advise on how to deal with this vendor?
Right now there are no procedures or function -- just native sql code on the theory everything is faster
and more efficient? May be?
Thanks for any information.
David Spaisman
View 1 Replies
View Related
May 16, 2004
Hello, everyone:
This is testing question from a tesing seb site. Any reply will be appreciated.
ZYT
View 3 Replies
View Related
Jul 20, 2005
Hello,I am starting to examine using SQLServer instead of Oracle. I wentthrough the SQLServer import utility to copy tables from Oracle intoSQLServer. I then checked the size of the database.I then started to specify indices for the tables. I did not see thedatabase file size grow all that much. I kept on using the shrinkcommand to make sure that wasted space was removed. I even made surethat minimal free space was allowed.I was rather expecting (from experience with Oracle) that there wouldbe a large growth in the database when addind indices. So what did Imiss? Are the indices stored elsewhere and I missed them? DoesSQLServer handle indices differently so they dont' bloat the databasesize?The size of our data in SQLServer is a key factor in whether we movefrom Oracle, so I need to under how indices effect storage size.Thanks for any help.Bart TorbertJoin Bytes!
View 7 Replies
View Related
Feb 22, 2006
Hi,
Apologies if this has been asked before, i've done a search but can't find a definitive answer.
I've created a table in an SQLExpress 2005 db using Server Managment Studio Express.
My intention is to use GUID fields as surrogate PK's. I therefore wanted to add a additional index to prevent duplicate records being added to the table. Not having used SQLServer before could someone confirm or deny that this is the correct way to do this. The PK field [EPISODEID{unique identifier}] is set as a non-clustered index. And i've created a second clustered index using the two fields that create a unique record. I've added a screen shot if that is any help.
Thanks
Chris
View 2 Replies
View Related
Feb 20, 2015
Is there a script to find which non-clustered indices are replicated? I know i can do this easily through GUI , having a script will make my life much easier ....
View 0 Replies
View Related
Aug 8, 2015
I'm looking for a quick script that someone has already written to update statistics (not to rebuild or re-organise) on specific indices in specific databases - I guess loop though a table comprising of a list of databases and the indices.
I know Ola has one but I'm not look for something that is that complicated. If I cannot find one I'm going to have to write one myself - I want to try and avoid re-inventing the wheel as tomorrow I have to do this work and it's about 7K plus indices in about 10+ databases.
View 2 Replies
View Related
Oct 9, 2006
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies
View Related
Dec 28, 1999
Hi folks.
Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2
then execute bellow statements
select * from t1
this query gives you table with out data
select * from t2
you will recieve an error that there is no object
but if you rollback
T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Email Address:
myself@ramkistuff.8m.com
View 1 Replies
View Related
Sep 5, 2001
How can I drop Primary Key? If I do/don't have data in the table.Thanks!
Ravi.
View 2 Replies
View Related
Apr 26, 2007
Why can't I drop the database? Is this query correct Drop database databasename?
View 1 Replies
View Related
Jul 28, 2007
Dear Friends,
i want to drop UDD.which i have used in 1000 of sps and tables. it is impossible to drop all objects and create.so please give any sugessions.
View 1 Replies
View Related
Sep 7, 2007
Hi,
I have an sql script, which updates some values by using BIT operations..
ex:
UPDATE table1
SET myValue = myValue & ~mask | (availability - mask)...
Problem is, this returns a value, when written to binary, it's one bit too long!
ex:
0
1
1
1
1
1
(= 62)
which should be:
0
1
1
1
1
0
(=31)
How can I "drop" this last BIT?
(Keep in mind that I store these values as LONGINT's)...
Been fighting with this one all day..
View 1 Replies
View Related
Oct 14, 2006
Hi,I have 30 Table with same structure but it will accept different value at RunTime according to AirCraftType. I wand to create a stored prosedure that will accept TableName, FlightDate, FlightNo. whenever i wnat to execute this code in the database I received error :Must declare the variable '@TableName'create proc InsertDataForAirBus330 @FlightNo varchar(5),@TableName varchr(5),@FlightDate datetime
as
insert into @TableName values(@JourneyDate,@FlightNo,18,42,280,3,7,35)plz suggest me how to solve this pboblem...Jasim...
View 2 Replies
View Related
Aug 13, 2007
How to create a sql statement that dynamically matches a database name in a stored proc. Somthing like this:
Select Table_name
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0
and table_name like 'Item%Master'
and (DATABASENAME=????????????????)
View 6 Replies
View Related
Jun 7, 2008
Hi....I am fresh to asp.net .... i want to get the Request.QueryString Value Dynamically in SQL Query ... I have attached the coding of two files please gothrough and give the solution..First File : -actyearwise.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actyearwise.aspx.cs" Inherits="acts_actyearwise" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:FormView ID="FormView1" AllowPaging="false" DataSourceID="AccessDataSource1" Width="80%" align="center" runat="server"> <ItemTemplate> <tr style="font-family: Arial; font-size: small;"> <td align="left" width="7%"> <a id="href1" href="actcheck.aspx?2007" target="_self">2007</a> </td> <td align="left" width="7%"> <a id="A1" href="actcheck.aspx?2006" target="_self">2006 </a> </td> <td align="left" width="7%"> <a id="A2" href="actcheck.aspx?2005" target="_self">2005 </a> </td> <td align="left" width="7%"> <a id="A3" href="actcheck.aspx?2004" target="_self">2004 </a> </td> </tr> </ItemTemplate> </asp:FormView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex "></asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actyearwise.aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actyearwise : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { }}// This page works Properly... In the actyearwise.aspx ..just some years with link... i have entered ...the href page is actcheck.aspx(below).. In this page i want the years dynamically.. please go throughSecond file : -actcheck. aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actcheck.aspx.cs" Inherits="acts_actfiles_actcheck" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" DataKeyNames="actno" DataSourceID="AccessDataSource1" Width="100%" AllowSorting="true"> <Columns> <asp:BoundField HeaderText="actno" Visible="False"/> <asp:TemplateField SortExpression="actno"> <ItemTemplate> <table width="80%" align="center"> <tr> <td align="left" width="3%" colspan="3"> <%# Eval("actno") %> </td> <td align="left" width="75%"> <a id="href1" href="actshorttitle.aspx?<%# Eval("actid") %>" target="_self"> <%# Eval("actname") %> </a> </td> </tr> </table> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex where actyear = 'check' "> </asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actcheck. aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actfiles_actcheck : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { String check = Request.QueryString.ToString(); } protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { }} The text i had bold and italics in the actcheck.aspx... is the problem..... From the actyearwise.aspx .. i was passing the actyear value ..... In actcheck,aspx .. i want to get it dynamically in SQL Query...... Please give the solution .. I am using the SQL database.Table name actindexfields nameactnoactnameactidactyear In the actyear field i have different years .... Thanks in advance
View 5 Replies
View Related
Apr 23, 2002
I'm trying to dynamically set the TOP value in a query, but it doesn't work. Any other way it can be done?
Doesn't work:
declare @sometopvalue int
set @sometopvalue = 100
select top @sometopvalue
field1,
field2,
field3
from table1 ORDER BY BRDCST_DT_TM field1 desc
View 1 Replies
View Related
May 19, 2004
Hi Guys: I am trying to get a top parameter dynamically but it does not seem to work
SELECT TOP
(SELECT COUNT(DISTINCT RegionID) AS COUNT
FROM Sales)
*
FROM Sales
but when I hard code the value it works
SELECT TOP
2
*
FROM Sales
any ideas...Thanks
View 6 Replies
View Related
May 2, 2014
I have a query that pulls many values and I want to loop through each value dynamically using a query.
SELECT COL1 FROM TABLE1
Results:
A
B
C
D
I want those results to loop and run this:
SELECT * FROM TABLE2 WHERE COL2 = A
SELECT * FROM TABLE2 WHERE COL2 = B
SELECT * FROM TABLE2 WHERE COL2 = C
SELECT * FROM TABLE2 WHERE COL2 = D
View 2 Replies
View Related
Sep 2, 2005
Hello,
I am trying to create a sp dynamically. However each time, I run my sp, which creates the dynamic SQL for the sp, then it automatically changes:
CREATE PROCEDURE .....
to
Alter PROCEDURE ....
Then I am obvoiusly getting the error msg:
Invalid object name.
How can I get rid of that?
View 14 Replies
View Related
Oct 18, 2006
HI chaps
my scenario is that, i have a table in which i have column such as col01,col02,col03....col31
i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ('col??') and i have to do integer increment in last two character (???01...???31). Is it possible that I can use variable for column name?, if yes how? or is there any other way to achieve this task ?
waiting for your reply
regards
View 7 Replies
View Related
Jul 26, 2007
Hi,
Using a ssis package I have several tasks.
A variable is also created called FileName.
There is a flat file connection called InputFileName.
initially when I designed the package, I hardcoded the pass of one of the .csv files into the path of the flatfile connection.
There is a dataflow which has flatfile source and oledb destination. These two basically pickup the file and import into the database using mapping.
So therefore the package works and data gets imported into the database.
Then I used the variable to make the filename dynamic. So I removed the hardcoded connectionstring in the InputFileName.
Is this the right thing to do to make the package dynamic? I would like to pass in any .csv filename and so the package runs.
My question is now that the connectionstring is deleted but instead placed an expression for the filename, then how does the system know which file to use?
Thanks
View 1 Replies
View Related
Mar 5, 2007
Hi, there;
I am doing a SSIS project, my target is:
I have a source folder which have some *.dbf files (the number of
files will change as time goes on) exported from another application. I want my SSIS package to load data from these dbf
files in the source folder to my SQL2005 database no mather how many
dbf files will be.If the destination table already exists in the database,
the logic logic will do update (delete and import again), if not, create a new table on th fly and then
pump the data.
What I did here is that I have a foreach loop container which contains Script
task, Execute SQL task and a data flow. The Foreach container now can
connect to the *.dbf files successfully, the Script task can read
schema information from each dbf file and create script "Create table ...."
successfully and Execute SQL task can create table in my new empty
database successfully! But I have problem in the data flow task: my OLEDB Source points to the dbf file folder and set Data Access mode to "Table name or view name variable",but when I set Variable Name to a package level variable ("tableName"), I get error: Error at Data Flow Task_Pumping data from Admar [OLE DB Source [2737]]: A destination table name has not been provided.
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
Does anybody know what to do to create table and pumping data on the fly? (Using variable as table name)
Big Thanks here.
View 2 Replies
View Related
Aug 13, 2007
I need an sql statement that dynamically matches a database name in a stored proc. Here is my attempt
Select Table_name
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0
and table_name like 'Item%'
and (DATABASENAME=????????????????)
Can someone help me out?
Thanks.
View 4 Replies
View Related
Aug 18, 2006
I have an FTP Task where I have to set the server password at run-time. I have the ftp connection manager set up. What I normally do is configure the ftp server connection manager using a dtsConfig file but I can't do that in this case. I won't know the serverpassword until I get a parameter from the user.
View 3 Replies
View Related
Apr 11, 2008
I have CSV file as source for SSIS package every time the filename will be changing like trd_1990M1_1990M12.csv
trd_1991M1_1991M12.csv , trd_1992M1_1992M12.csv so it will vary as per user selection . i need to run the same SSIS package to execute the package whichever the file name.
Please let me know the solution for that how to pass the file name dynamically to SSIS package.
View 3 Replies
View Related
Feb 12, 2007
Apologies in advance for the newbie question. I'm new to Integration Services and am trying to build my first project.
I want to dynamically create folders based on the results of a stored procedure. The resultset is limited to one column but can contain multiple rows. (It returns a list of 8-character strings representing dates.) This returned value needs to be combined with the path where the new folders should be created.
Can someone point me in the right direction? I've run the stored procedure and put the resultset into a RecordSet Destination that uses a variable of type Object. (At least, I think I've done it correctly.) But I have not been able to figure out how to access this data to actually create the folders.
Thanks for the help,
Chris
View 3 Replies
View Related
Apr 30, 2000
Hi folks,
Wonder if I'm onto something....everything I've read says that to drop foreign key constraints, you have to drop/recreate the table.
I've been able to dynamically create the ALTER TABLE statements for all FK constraints in a database. The way I did was a little convoluted (Access 97 linked tables and pass-through queries, cut/paste from the debug window to SQL7 QA), but it does work. More importantly, it saves the hassle of dropping/recreating tables....Someone with more expertise than I in using nested cursors could probably convert it to pure T-SQL pretty easily.
Just curious...opinions? Be glad to post the code here if that's appropriate.
Dan
View 1 Replies
View Related
Feb 14, 2000
Does anyone know why this does not work?
ALTER TABLE deal DROP COLUMN TestField
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TestField'.
Thanks,
Paul
View 4 Replies
View Related
Mar 2, 2000
I am trying to drop some tables, but keep getting the message:
Msg 3702, Level 16, State 1
Cannot drop the table 'TABLE' because it is currently in use. But it is NOT IN USE. I tried to do an sp_who, and I'm the only one. Please help. Thank you.
View 1 Replies
View Related
