Rebuilding Table Indexes
In T-SQL, you reindex all the indexes in the table using the
DBCC DBREINDEX statement or
ALTER INDEX statement. Currently, Babelfish doesn’t support those statements. Instead, you can run the PostgreSQL
REINDEX TABLE statement from a Postgres connection. For information about the PostgreSQL statement, see
REINDEX in the PostgreSQL documentation.
If your T-SQL maintenance scripts have a statement like one of the following:
DBCC DBREINDEX ('my_database.dbo.my_table');
ALTER INDEX ALL ON my_database.dbo.my_table REBUILD;
then run this equivalent statement, while connected to the PostgreSQL port of your Babelfish-enabled server:
REINDEX TABLE my_database_dbo.my_table; -- For servers using multi-database mode
REINDEX TABLE dbo.my_table; -- For servers using single-database mode
For convenience, you can define a PostgreSQL stored procedure that runs the
REINDEX TABLE statement for a specified statement, and call the stored procedure using the T-SQL
CALL statement. If you do so, make sure that the permissions to call the procedure are consistent with the permissions to perform DDL operations on the table, and that any dynamically created
REINDEX TABLE statements quote all the schema and table names to avoid a malformed statement.
For information about using Babelfish from both the TDS port and the PostgreSQL port, visit the Babelfish website.