Source Table Schema on a different server

Question

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..

Answer 1

Erland Sommarskog 72,341 MVP

Here is another option:

EXEC LinkedServer.db.sys.sp_help 'dbo.MyTable'

Answer 2

Guoxiong 8,056

SELECT SCHEMA_NAME(schema_id) AS Schema_Name, name AS Table_Name
FROM [LINKED_SERVER_NAME].[DATABASE_NAME].sys.tables;

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,056

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,056 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,566 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.

Source Table Schema on a different server

2 answers

Activity