Table Index Store and Create

Question

Table Index Store and Create

Jorge Novo-@ETLDEVDBA

Ten Centuries

Points: 1265
More actions
July 12, 2009 at 11:15 am

#384974

Comments posted to this topic are about the item Table Index Store and Create

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ajithezk SSC Rookie Points: 41 More actions · Answer 1

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

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 2

declare @cmd varchar (max)

Select @cmd= "Create index "+IndexName + "......From tblDBAIdxMetadt

exec (@cmd)

You got the idea 🙂 I will post a better Query tomorrow.

ajithezk SSC Rookie Points: 41 More actions · Answer 3

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

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 4

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.

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 5

Here is a better example of how to restore the indexes :

If Exists (select distinct

SchemaOwner

,TableName

,AllColName

from tblDBAIdxMetadt

where IsClustered = 1

and 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)

end

You can used a cursor or while loop to generated or execute the scripts by filling the information with the ? marks.

Jorge Novo-@ETLDEVDBA Ten Centuries Points: 1265 More actions · Answer 6

This will be a way to save relations from a table.

--Create Table to store constraint information

IF 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 Constraints

insert 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) FKOrder

FROM sysforeignkeys sf

INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid

INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid

ORDER BY rkeyid,fkeyid,keyno

Ed Lyons SSC Enthusiast Points: 146 More actions · Answer 7

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 108

Incorrect 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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 8

akljfhnlaflkj

SSC Guru

Points: 76202

May 24, 2016 at 7:04 am

#1881068

Thanks for the script.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Iwas Bornready (5/24/2016)
Thanks for the script.

Maybe you should see if you can rewrite the script to use the system views instead of the SQL 2000 compatibility views. That would be more worthwhile than just these ridiculous "Thanks for the script" posts of yours to pad your post count.