September 18, 2009 at 5:45 am
Hello,
I'm have a problem with upgrading databases. I'm creating a program which will analyze one database and store the information in a Xml file, The next step should be analyze another database and to correct anything which is not there.
This should alter tables when there are fields missing, create tables if they are missing completely and create Indexes, triggers etc.
I found a way to extract the Tables and there column information using the SqlConnection.GetSchema("Columns") function.
and I found the stored procedures, functions and triggers in the combination of sysObjects and sysComments using the following query:
SELECT t1.name, t1.category, t2.text
FROM sysobjects t1 INNER JOIN
syscomments t2 ON t1.id = t2.id
WHERE (t1.xtype = 'P' OR
t1.xtype = 'FN' OR
t1.xtype = 'IF' OR
t1.xtype = 'TF' OR
t1.xtype = 'TR') AND (t1.category = 0)
I assume that the category = 0 is the user create stuff (that's valid for all the database I have analyzed)
I can find the Indexnames using the SqlConnection.GetSchema("Indexes").
But I can't find the actual alter table command that I need to create the index.
I also can't find the information which fields in which order are part of the index which would give me the needed information to create the Indexcommands myself.
greetings,
Rudy
September 18, 2009 at 12:37 pm
In SS2K5 look at sys.index_columns view.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 18, 2009 at 12:45 pm
I don't know if this will help much or not; however, I'll give it a shot.
I would go under SSMS, open the relevant database. Expand the tables, Expand the specific table, expand the index, right click on the index and select script to new query window. This generated SQL should contain the ALTER index statement.
Here is an example of what I get.
[Code]
USE [Tennis]
GO
/****** Object: Index [PK__PENALTIES__0AD2A005] Script Date: 09/18/2009 14:44:17 ******/
ALTER TABLE [dbo].[PENALTIES] ADD PRIMARY KEY CLUSTERED
(
[PAYMENTNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
[/code]
September 18, 2009 at 2:06 pm
Hello,
we are using sql server 2000 for most of the installations so the sys.index_column isn't an option.
I want to get the index information from within a c# program which is creating a xml file describing the database so using the Enterprise Manager or the Query analyzer to retrieve the information isn't an option. I did try to use the Profiler to trace the database interaction when requesting an index description from within the Query analyzer and the Enterprise manager, but so far I couldn't find the correct procedures or tables to rebuild the create index command from.
I hope this clarifies to question for you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply