Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table Index Store and Create Expand / Collapse
Author
Message
Posted Sunday, July 12, 2009 11:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
Comments posted to this topic are about the item Table Index Store and Create
Post #751736
Posted Wednesday, July 29, 2009 8:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 6:08 AM
Points: 11, Visits: 13
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
Post #762078
Posted Wednesday, July 29, 2009 9:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
declare @cmd varchar (max)

Select @cmd= "Create index "+IndexName + "......From tblDBAIdxMetadt
exec (@cmd)
You got the idea I will post a better Query tomorrow.
Post #762091
Posted Wednesday, July 29, 2009 9:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 6:08 AM
Points: 11, Visits: 13
Thanks for reply.. can we store the relations to the same table and can we restore it?
Post #762100
Posted Thursday, July 30, 2009 8:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
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.
Post #762460
Posted Thursday, July 30, 2009 8:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
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.
Post #762463
Posted Thursday, July 30, 2009 8:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
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
Post #762466
Posted Friday, September 11, 2009 12:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 6:44 AM
Points: 6, Visits: 80
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



Post #786630
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse