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 100.9K 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
Guoxiong 8,126 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,126 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,126 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,601 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