SQLServerCentral / Discuss content posted by Jorge Novo / Article Discussions / Article Discussions by Author / Table Index Store and Create / Latest Posts

RE: Table Index Store and Create

Ed Lyons — Fri, 11 Sep 2009 12:20:15 GMT

Running the code as is, and adding a tablename from a SQL Server 2005 database, gives the following error:Msg 102, Level 15, State 1, Line 108Incorrect syntax near 'end'. Any ideas what's wrong. The syntax looks fine. I'm using the SQL Server query editor and selecting the database from the combo box in the menu bar at the top of the editor.Thanks,Ed

RE: Table Index Store and Create

Jorge Novo-@ETLDEVDBA — Thu, 30 Jul 2009 08:51:12 GMT

This will be a way to save relations from a table.--Create Table to store constraint informationIF NOT EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')Create table DatabaseName.dbo.T_FK_Xref (ID int identity (1,1),ConstraintName varchar (255),MasterTable varchar(255),MasterColumn varchar(255),ChildTable varchar(255),ChildColumn varchar(255),FKOrder int)go--Store Constraintsinsert into DatabaseName.dbo.T_FK_Xref(ConstraintName,MasterTable,MasterColumn,ChildTable,ChildColumn,FKOrder)SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable,sc2.name MasterColumn,object_name(fkeyid) ChildTable,sc1.name ChildColumn,cast (sf.keyno as int) FKOrderFROM sysforeignkeys sfINNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colidINNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colidORDER BY rkeyid,fkeyid,keyno

RE: Table Index Store and Create

Jorge Novo-@ETLDEVDBA — Thu, 30 Jul 2009 08:46:21 GMT

Here is a better example of how to restore the indexes :If Exists (select distinctSchemaOwner,TableName,AllColName from tblDBAIdxMetadtwhere IsClustered = 1and UniqueType <> 'PRIMARY KEY')begin declare @IndexName varchar(255) ,@SchemaOwner varchar (255) ,@TableName varchar (255) ,@ColumnList varchar (1000) ,@Sqlcmd varchar (2000)set @IndexName = ?set @SchemaOwner = ?set @TableName = ?set @ColumnList = ?select @sqlcmd = 'CREATE CLUSTERED INDEX '+ltrim(rtrim(@IndexName))+' ON '+ltrim(rtrim(@SchemaOwner))+'.'+ltrim(rtrim(@TableName))+' ( '+ltrim(rtrim(@ColumnList))+' ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 'exec (@sqlcmd)endYou can used a cursor or while loop to generated or execute the scripts by filling the information with the ? marks.

RE: Table Index Store and Create

Jorge Novo-@ETLDEVDBA — Thu, 30 Jul 2009 08:43:55 GMT

See scripts Truncate all Tables There you will find a script to save and restore the table relationships. Normally you want to keep that kind of data separated from it.

RE: Table Index Store and Create

ajithezk — Wed, 29 Jul 2009 21:40:05 GMT

Thanks for reply.. can we store the relations to the same table and can we restore it?

RE: Table Index Store and Create

Jorge Novo-@ETLDEVDBA — Wed, 29 Jul 2009 21:16:39 GMT

declare @cmd varchar (max)Select @cmd= "Create index "+IndexName + "......From tblDBAIdxMetadtexec (@cmd)You got the idea :-) I will post a better Query tomorrow.

RE: Table Index Store and Create

ajithezk — Wed, 29 Jul 2009 20:34:48 GMT

thanks.. very good post.. This for storing all index to table, after storing the informtion to table, how to restore it when i needed? Please let me know the query for restoring the indexes from newly created table data

Table Index Store and Create

Jorge Novo-@ETLDEVDBA — Sun, 12 Jul 2009 11:15:23 GMT

Comments posted to this topic are about the item [B]Table Index Store and Create[/B]