April 7, 2016 at 8:21 pm
Hello, I need to find out the unique values in each column from the tables in a linked server. I have about 30 columns and 30 tables in the linked server. I do not know which table has which columns. Can someone help me with it? Thank you very much. Olivia
April 8, 2016 at 6:27 am
xqzuo72 (4/7/2016)
Hello, I need to find out the unique values in each column from the tables in a linked server. I have about 30 columns and 30 tables in the linked server. I do not know which table has which columns. Can someone help me with it? Thank you very much. Olivia
So ... you know 30 tables and 30 column names, but you do not know which column name belongs to which table. This seems like a rather opaque requirement.
1) Get a list of all column names for each of the tables?
SELECT
c.name
FROM sys.columns c
WHERE OBJECT_SCHEMA_NAME(c.object_id) = 'TableName'
AND OBJECT_NAME(c.object_id) = 'SchemaName';
(insert your own values for TableName and SchemaName
2) Once you know which column lives on which table, you can do this:
select distinct [ColName] from [TableName]
for each identified column.
April 8, 2016 at 7:40 am
Hello, Phil, Thank you very much for your help! I am a beginner of SQL. I use MS SQL server 2014.
I tried your code and substitute my table and column name, it said Query Executed successfully, but I only see Name came out. The tables are in a linked server called "Net". Should I somehow tell where to look for the table in the linked server?
In addition, it seems I cannot enter more than 1 table at a time, do I need to check it one by one? some of the table has very long names?
Can you please give me more help? Again, thank you.
SELECT
c.Name
FROM sys.columns c
WHERE OBJECT_SCHEMA_NAME(c.object_id) = 'BREED'
AND OBJECT_NAME(c.object_id) = 'BREED_ID';
April 8, 2016 at 8:00 am
xqzuo72 (4/8/2016)
Hello, Phil, Thank you very much for your help! I am a beginner of SQL. I use MS SQL server 2014.I tried your code and substitute my table and column name, it said Query Executed successfully, but I only see Name came out. The tables are in a linked server called "Net". Should I somehow tell where to look for the table in the linked server?
In addition, it seems I cannot enter more than 1 table at a time, do I need to check it one by one? some of the table has very long names?
Can you please give me more help? Again, thank you.
SELECT
c.Name
FROM sys.columns c
WHERE OBJECT_SCHEMA_NAME(c.object_id) = 'BREED'
AND OBJECT_NAME(c.object_id) = 'BREED_ID';
OK, because you are using a linked server, you need to expand the syntax a little to use a fully qualified table name:
SELECT c.Name
FROM linkedservername.databasename.sys.columns c
WHERE OBJECT_SCHEMA_NAME(c.object_id) = 'BREED'
AND OBJECT_NAME(c.object_id) = 'BREED_ID';
Replace linkedservername with 'net' and replace databasename with, err, the name of the database.
To avoid having to type long table names, if you can see them in the Object Explorer in SSMS (SQL Server Management Studio), you can click and drag them onto your query pane.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply