Source Table Schema on a different server

VS29 246

Hi All,

can someone please suggest how I can get the schema of a source table along with constraints while I only have access to source table using linked server only.

thanks in advance..

2 answers

Erland Sommarskog 115.6K Reputation points MVP

2021-07-13T21:38:55.54+00:00
Here is another option:

EXEC LinkedServer.db.sys.sp_help 'dbo.MyTable'
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Guoxiong 8,206 Reputation points

2021-07-13T19:01:27.057+00:00
SELECT SCHEMA_NAME(schema_id) AS Schema_Name, name AS Table_Name FROM [LINKED_SERVER_NAME].[DATABASE_NAME].sys.tables;
Please sign in to rate this answer.
VS29 246 Reputation points

2021-07-13T19:08:27.297+00:00

hi @Guoxiong , Thank you for the reply.
I am looking for the data types and also any default constraints that table has.

Guoxiong 8,206 Reputation points

2021-07-13T19:37:54.453+00:00

Try this:

SELECT col.name AS Column_Name, ty.name AS Data_Type, dfc.name AS Default_Constraint FROM [LINKED_SERVER_NAME].[DATABASE_NAME].sys.tables AS tbl INNER JOIN [LINKED_SERVER_NAME].[DATABASE_NAME].sys.columns AS col ON col.object_Id = tbl.object_Id INNER JOIN [LINKED_SERVER_NAME].[DATABASE_NAME].sys.types AS ty ON col.system_type_id = ty.system_type_id LEFT JOIN [LINKED_SERVER_NAME].[DATABASE_NAME].sys.default_constraints AS dfc ON tbl.object_Id = dfc.parent_object_Id AND col.column_id = dfc.parent_column_id WHERE tbl.name = 'TABLE_NAME';

VS29 246 Reputation points

2021-07-13T19:44:34.607+00:00

it worked, thank you @Guoxiong !!

Guoxiong 8,206 Reputation points

2021-07-13T20:19:49.517+00:00

Good to hear it helped. Could you please "Accept Answer" so that it will benefit all community members who are having this similar question? Thanks.

Cris Zhan-MSFT 6,641 Reputation points

2021-07-14T06:25:54.523+00:00

Hi,
I’m glad to see that your question has been resolved. In order to close this thread, please click "Accept Answer" and upvote on GuoxiongYuan-7218's answer.

By the way you can add dfc.definition to the query to view the definition of the default constraints.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Source Table Schema on a different server

2 answers

Your answer