1

I am building a spatial view using SQL Server Management Studio 2014. One of the tables I am using to build this view has field names that are not compatible with esri; the field names have slashes, hyphens and spaces.

Is it possible to rename the fields of the view being created as the view is being created? So something like;

CREATE VIEW dbo.NewView AS
SELECT * FROM
dbo.tableA
INNER JOIN dbo.tableb
ON dbo.tableA.UID=dbo.tableB.UID
EXEC sp_rename 'dbo.NewView.Bad/field-name','GOOD_FIELD_NAME'

The EXEC sp_rename works if I run it by itself after the view has been created and on one field at a time, which isn't an ideal solution.

Or is there some way to run a find/replace on the field names of the view after it has been created?

2

A few things:

Alias your fields in your select, and don't use the select * from when building views for ArcMap.

So your view SQL would look like:

CREATE VIEW dbo.NewView AS
SELECT 
tableA.field1 as field1Alias
, tableA.field2 as field2Alias
, etc. etc. etc.
FROM
dbo.tableA as tableA
INNER JOIN dbo.tableb
ON dbo.tableA.UID=dbo.tableB.UID

I'm not sure you have to run that procedure to change the names - my experience is the field name aliases do the trick.

Also - by explicitly selecting the fields you need, you don't run into any problems with ArcMap and the select * problem

ALSO: I've recently come across the ability to create a new table from SQL in MSSQL using the 'into newtablename' method, and you might try doing something like this instead of creating a view:

    SELECT 
    tableA.field1 as field1Alias
    , tableA.field2 as field2Alias
    , etc. etc. etc.
into dbo.NewTable
    FROM
    dbo.tableA as tableA
    INNER JOIN dbo.tableb
    ON dbo.tableA.UID=dbo.tableB.UID

We'll be using the 'into' method instead of using complex and time-consuming views - ArcGIS can take several times longer to compute a view than SQL Server takes, so by creating a static table, it is much easier to work with in ArcMap. And to update that table, you can simply add a 'drop table newtablename' to the beginning of the SQL statement.

  • @Cjd111 excellent! – DPSSpatial Oct 28 '15 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.