How to script all the PK/FK/constrainnts/indexes with create and drop statements?
As you know, we can't script 'drop statements' for primary keys etc..
Any help in giving me a script which does all the above is greatley appreciated..
Thanks,
Di.
I am new to writing SQL code and I read that you can use ALTER statements to create an index for a table. How would I go about doing that? Everything that I have tried in Query Analyzer comes up with an error.
Any help is appreciated.
For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?
View 4 Replies View RelatedHi,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 RelatedI'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
I have a database with many PK and FK constraints. Is there any script I can use to drop all indexes and rebuild them taking care of PK and dependencies? I am unable to drop them by tablename order. This is on MSSQLServer-2000 SP3. Any help is appreciated.
View 4 Replies View Relatedis there a way to drop all the indexes on a table other than listing them out separately in a drop index statement?
View 2 Replies View RelatedHow can I drop all the indexes in all the tables in a MS 2000 SQL Server?
View 9 Replies View RelatedI'm trying to drop all indexes and primary keys so that i can rebuild them(from a script created from same database on another server).when i go to the 'generate sql scripts', it has the ability to drop orgenerate alltables. it also has the ability to generate all keys only. but i cant finda wayto drop all of these keys...any ideas?tiawoody rao
View 1 Replies View RelatedI have come across a user that has error 2525 errors on sysobjects tables throughout their SQL 6.5 system. The ids match and I can identify the index. But as they are system tables I cannot drop or rebuild the indexes.
Any ideas please?
The errors have been occuring for some time, so it is not an option to restore. I would like to avoid having to transfer data to and from another SQL Server.
Many Thanks
I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?
View 3 Replies View RelatedI created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
Hi
I am wana to know why we every time drop the procedure and again create it as below...will that impact on performance or compilation of SP???
Use Northwind
GO
IF OBJECT_ID('dbo.ListCustomersByCity') IS NOT NULL
DROP PROC dbo.ListCustomersByCity
GO
CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%'
AS
SELECT City, COUNT(*) AS NumberOfCustomers
FROM Customers
WHERE Country LIKE @Country
GROUP BY City
GO
T.I.A
When creating the indexes on the table, SQL 7.0 also created some type of system indexes ( ex. _WA_Sys_curr_int_rate_2C88998B nonclustered, statistics, auto create located on PRIMARY, etc. ). I want to know how can I clear up these system indexes.
View 1 Replies View RelatedSlayer writes "Probably a really lame question but in the absence of our DBA I have been asked to create a couple of indexes on a table. No problem with that, just need to know if this is a safe operation whilst users are using the database.
Thanks for your help"
What kind of problems may I see if a process drop and re-create a set of tables every night?
Hello,
What's the command for dropping an existing Foreign key on a table and reacreating it in SQL Server 6.5
Thanks
Sri
Hi,
I had a question and did not obtain an answer. So I am trying to rephrase and ask again, in case I was not clear the first time.
When we drop and recreate a table, do we always have to recompile the stored procedures that reference the table? Or is only under certain scenarios that we need to do so- like if an index on the table is changed
Please let me know
Thanks in advance
Kiran
I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time.
I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?
Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...
Thanks
Hi all,
I have 2 questions:
1. How can I drop /remove a complete db from MSDE desktop engine?
2. After exporting my db onto MSDE server and get connected those db tables in FE (adp), how can I create a new table in the same back end db?
I know these are basic questions but since I am new to MSDE I hope the forum would bear me.
With kind regards,
Ashfaque
Hi,
How can i create and drop table in MS SQL Server 2000 via VB6? I think I should use ADOX object, but I don't know exactly how....
The following code uses ADO connection object and returns with runtime error "incorrect syntax near AS":
Dim db as ADODB.Connection
'... open connection to database
Dim strCmd As String
strCmd = "CREATE TABLE tmp_tbl AS SELECT * FROM tbl"
db.Execute strCmd
Thank you in advance
Hi, all,
I want to re-create an index on a production table.
I got an error 644 "could not find index entry...".
The DBCC CHECKDB and CHECKTABLE gave me this:
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 37575172, index ID 6: Page (1:939782) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 37575172, index ID 6, page (1:939782). Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 7 and 36.
DBCC results for 'Mfg_DFSFNSF'.
There are 1142314 rows in 326143 pages for object 'Mfg_DFSFNSF'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Mfg_DFSFNSF' (object ID 37575172).
The table script for the index is like this:
CREATE UNIQUE INDEX [Mfg_ITMDH_MbrIdx] ON [dbo].[Mfg_DFSFNSF]([_ITMDH_OwnRow], [_ITMDH_MbrKey], [RECTYPE]) WITH FILLFACTOR = 70 ON [PRIMARY]
My question is that can I drop it and run above create it to fix the problem in live mode?
I know the other option will be:
DBCC CHECKTABLE (FSDBMR.dbo.Mfg_DFSFNSF, repair_allow_data_loss)
But that has to put the db under single user mode.
Thanks!
Hello,
A few months ago, the company I work for switched to SQL 7.0 from 6.5. All is running fine but recently I noticed that we have a ton of indexes on each table that start like this: _WA_SYS_....0B679CE2 (here are some specific examples: _WA_SYS_PRODUCT_LINE_0B679CE2, _WA_SYS_RMA_DISPOSITION_0B679CE2)
Did SQL 7.0 do this? If so why? What is it trying to do? Why doesn't it use current indexes (clustered and non-clustered)? Why don't they get Fragmented like User defined indexes? I have a lot of questions associated with this if someone can explain it.
Tech Net, MSDN, BOL and other manual resources don't mention anything about it. At least I haven't found anything on it and I have spent some time looking.
Please help if you can.
Troy
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.
I found out how to create clustered index using TSQL, but how can you create clustered index and manage indexes using SQL Server Management Studio?
Thanks
Goldmember
Hi,I have a new job. It needs to drop and re-create (by insert) a tableevery night. The table contains approximately 3,000,000 (and growing)records. The insert is fine, runs in 2 minutes. The problem is thatwhen I create the indexes on the table, it is taking 15-20 minutes.There is one clustered index and 11 non-clustered. This is a lookuptable that takes many different paremeters, so it really needs theindexes for the user interface to run efficiently. However, thedatabase owners aren't keen on a job taking 20 minutes to run everynight.Any ideas?
View 5 Replies View RelatedFirst, I'd like some help stablishing if it's a good idea or not what I'm thinking, and second is what would be the easyist way to achive the result.
Background. We have a multicompany ERP system (200GB, 200+ tables). All data tables have a companyid, counter1id, counter2id, fields. Each table has a primary clusterd index on these fields in that order. And also has multiple other indexes that usually include companyid filed first. ( Note we have 10 companies, ids are 1..10, companies 2 and 5 are the most active, inserts, selects ). MY GUESS IS THAT THIS IS VERY BAD HAVING COMPANYID FIRST. AM I CORRECT? LEADS TO AL LOT OF FRAGMENTATION? VERY BAD TO FIND DATA ON THE INDEXES?
I want to re-create all my indexes and have them counter2id, companyid, counter2id. ( counter1id is a counter for each company, and counter2id is a global counter like identity ) GOOD OR BAD IDEA?
Since I have hundreds of indexes what is the correct procedure? I was thinking on scripting the database, then dropping all indexes, then recreate clustered indexes, then recreate other indexes. Do I have to take care of anything else foreing relationships, etc.
I have some problems getting my SSIS package to run in a transaction, I wonder if anyone can assist.
What I want to do is run one package, which consists of a
- 1) create staging table SQL statement
- 2) read (all) from Access MDB to staging
- 3) copy (only new) from staging table to real table
- 4) drop the staging table
I changed the package to "require" a transactions, and left all containers in it to "supported" (default, I think). I left all the transaction type to default "serializeable".
The package does not work, since the table I created on step one is not "seen" when step 2 wants to insert into it. If I set the package transaction back to "supported" (default) the package works, but an error on step 2 or 3 will not rollback changes and not remove the staging tables...
So, my question really is: How do I make step 1 results visible to step 2 in the same transaction? Or do I need to take a completly different approach for this?
Stupid question I think, but I seem to not be able to find the right way to handle this situation...
Thanks for reading!
Ralf
Hi
I have written stored procedures to create a database with db name as input parameter
I need to create a batch file to run the stored procedure with input value given in the command prompt along with the batch file will be my dbname, which will be used by the stored procedure to create the databse.
and also few sql statements to insert data into those tables after creating
Plz could any one help me out to create code to create a batch file
thanks in advance
Hi What is the SQL syntax to create and destroy a stored proceedure with t-sql? Is there any good tutorials?ThanksPWP
View 5 Replies View RelatedIf I rebuild the clustered index using this option (Drop Existing) without specifying the Fill Factor, would it it retain the original Fill Factor?
Thank you
How to SQL server save information about create/drop objects action. How can I get this? Example for, a lot of objects(sp) in my database has been dropped, how I know who was dropped them (user login & time)?
View 1 Replies View RelatedHi All,
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name]
Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
Thanks,
Srinivas