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..
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..
Here is another option:
EXEC LinkedServer.db.sys.sp_help 'dbo.MyTable'
SELECT SCHEMA_NAME(schema_id) AS Schema_Name, name AS Table_Name
FROM [LINKED_SERVER_NAME].[DATABASE_NAME].sys.tables;
hi @GuoxiongYuan-7218 , Thank you for the reply.
I am looking for the data types and also any default constraints that table has.
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';
18 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index