Making Additions and Changes; Getting Error Preventing Them

Question

Making Additions and Changes; Getting Error Preventing Them

Viewing 4 posts - 16 through 18 (of 18 total)

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

Chris Wooding SSCarpal Tunnel Points: 4580 More actions · Answer 1

The square brackets are there in case the object name contains special characters such as a space, eg. [Birth Date]. If you name objects without special characters, eg. BirthDate, the square brackets are optional and most people would leave them out to improve readability. SSMS always puts the square brackets in when generating scripts.

I don't know why you are seeing differences in table structure between design mode and the object explorer. I've never had to use sp_refreshsqlmodule to resolve this. I can only suspect that you have more than one copy of the table (in different schemas) due to the multiple attempts to create/modify it.

Ahr Aitch SSC-Addicted Points: 415 More actions · Answer 2

Thanks, Chris.

I am still having problems with relationships. I make them and save the diagram, but when I return the relationship indicators are gone.

I did not have to run the sp_refreshsqlmodule. I don't remember how I resolved it. 83yo brains don't function like 25yo brains.

Do you know of a query that will show FK relationships added to tables. It would be great to see them in the tables displayed in diagrams. I found and experimented with Tools > Options > Environment but didn't find a way to do that; although, I was able to make the tables include data types and nullable blocks in diagrams.

Again, thanks for the response and information. I was puzzled by the 'sometimes' appearance of [ ] in syntax. Your explanation truly helps.

Be kind. Be calm. Be generous. Behave.

Chris Wooding SSCarpal Tunnel Points: 4580 More actions · Answer 3

This query will list the foreign keys defined in the database.

SELECT fk.name AS fk_name, ts.name AS sub_table, cs.name sub_table_column, tm.name AS master_table, cm.name AS master_table_column
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tm ON tm.object_id = fkc.referenced_object_id
INNER JOIN sys.tables ts ON ts.object_id = fk.parent_object_id
INNER JOIN sys.columns cs ON cs.object_id = ts.object_id AND cs.column_id = fkc.parent_column_id
INNER JOIN sys.columns cm ON cm.object_id = tm.object_id AND cm.column_id = fkc.referenced_column_id
ORDER BY ts.name, fk.name, fkc.constraint_column_id

Ahr Aitch SSC-Addicted Points: 415 More actions · Answer 4

Thank you, Chris. That helps immensely.

Be kind. Be calm. Be generous. Behave.

Making Additions and Changes; Getting Error Preventing Them

Cookies on SQLServerCentral