question

VS29 avatar image
0 Votes"
VS29 asked Criszhan-msft commented

Source Table Schema on a different server

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



sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered Criszhan-msft commented

 SELECT SCHEMA_NAME(schema_id) AS Schema_Name, name AS Table_Name
 FROM [LINKED_SERVER_NAME].[DATABASE_NAME].sys.tables;
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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';
2 Votes 2 ·
VS29 avatar image VS29 GuoxiongYuan-7218 ·

it worked, thank you @GuoxiongYuan-7218 !!

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

Here is another option:

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.