-1

I have just started using and learning SQL server i am using a database wcm_staging (so tables in this database are like this - wcm_staging.dbo.cust_id, and there are many tables like this), I need to check the column names (variable names) in all the tables in this particular database. Can some one help please about how to do it?

Many thanks,

Best Ritika

pallares
  • 165
  • 4
  • 13
Ritika Tewari
  • 1
  • 1
  • 1
  • I think it's obvious what you mea, but if wcm_staging is the database-name, wcm_staging.dbo.cust_id cannot be a table. Cust_id is a field. Your missing the table-name. Have a look at http://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database – roberth Apr 10 '17 at 06:48

2 Answers2

2

This will list out all the table names and column names with data tyes

USE DATABASE NAME
GO
SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='TABLE NAME'
Chanukya
  • 5,685
  • 1
  • 18
  • 35
  • 1
    *This will list out all the table names* - uhm ... not in case of a `WHERE TABLE_NAME='TABLE NAME'` :-D – Shnugo Apr 10 '17 at 06:52
  • @Chanukya thanks for the help, it helped in getting the name of the columns for one table where table_name ="test1" but i was wondering is there a way of getting the column names of all tables in a particular database, this database has large number of tables, i need to look at the variables contained in those tables, can you please help?thanks for the above code though – Ritika Tewari Apr 10 '17 at 07:25
  • @Chanukya, so then the code would be Use database_name go select * from sys.columns .... without any where condition ??? – Ritika Tewari Apr 10 '17 at 07:36
  • if u want to check with particular table select b.name,a.NAME,max_length,precision,scale from sys.columns A join sys.tables B on a.object_id=b.object_id – Chanukya Apr 10 '17 at 07:43
  • @ Chanukya ,ya this is working, but i would need column names per tables in this database, is there any way i can do that? i mean name of all columns in each table, under this database, so that i know which table has what variables, thank you so much for the help so far :) – Ritika Tewari Apr 10 '17 at 07:51
  • @Chanukya, I dont want to check with particular table, i want names of variables table wise , for all the tables in a database, like the first code u shared in which we used information_schema. columns and a where condition for table name, the output of this code is for one table. where in the output there was a column name called 'table_name' which specified the table, can i get a similar output for all the tables, where the table name would also be mentioned – Ritika Tewari Apr 10 '17 at 08:35
2
SELECT 
OBJECT_NAME(object_id) AS [Table Name],
name AS [Column Name]
FROM sys.columns
ORDER BY 1
Raj
  • 10,433
  • 2
  • 43
  • 50