CODEWITHSUNDEEP
javasqlsql-server
Borislav Stefanov
Borislav Stefanov

Reputation: 143

Selecting default value SQL management studio

I have SQL Server and I am using SQL management studio. What I want is to select default value of column of multiple tables in database. For example I have database DB. In DB I have 3 tables - tableA, tableB and tableC. All of them have the same columns. What I want is to get default values of columns 'Customers' for tables A,B and C.

It must be something like this:

SELECT Column_Default 
FROM Information_Schema.Columns
 WHERE Table_Schema = DB AND 
 Table_Name like 'table%' and
 Column_Name = 'Customers'

But there is error in Table_Schema = DB. I am not sure it should look like this as SQL syntax. It is working as MySQL but not here.

Upvotes: 0

Views: 790

Answers (1)

WhatsThePoint
WhatsThePoint

Reputation: 3635

SELECT COLUMN_DEFAULT 
FROM   Information_Schema.Columns
WHERE  Table_Schema = 'dbo'  
  AND  TABLE_NAME IN ('A','B','C')  
  AND  COLUMN_NAME = 'Customers'

You were close, you just needed single quotes around your table schema name, also using the IN operator to explicitly specify your table names instead of using your wildcard search can make the query more robust if if a new table of a similar name is created.

Upvotes: 1

Related Questions