How can I find all Bit type columns in my database

I need to find all columns which are of type Bit in my database.

What SQL query would I need to return me that information?  I am using MS SQL Server v2008.
LVL 1
FrancineTaylorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
@eridanix: remember, not everyone's database is case insensitive. INFORMATION_SCHEMA views are all uppercase. sys.* is all lowercase. I would not mix the two anyway. For newer versions of SQL, use the INFORMATION_SCHEMA views. Therefore, if you need TABLES, then it is like this:
SELECT C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
JOIN INFORMATION_SCHEMA.TABLES T 
   ON T.TABLE_SCHEMA = C.TABLE_SCHEMA
   AND T.TABLE_NAME = C.TABLE_NAME
WHERE C.DATA_TYPE = 'bit'
AND T.TABLE_TYPE = 'BASE TABLE'
;

Open in new window

0
 
eridanixCommented:

USE [your_database_name]
SELECT C.TABLE_NAME, C.column_name
FROM sys.Tables T INNER JOIN
	INFORMATION_SCHEMA.COLUMNS C ON T.name = C.TABLE_NAME
WHERE C.DATA_TYPE = 'bit'

Open in new window

0
 
FrancineTaylorAuthor Commented:
Threw this error:

Invalid object name 'sys.Tables'
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
eridanixCommented:
So try this:
USE [your_database_name]
SELECT C.TABLE_NAME, C.column_name
FROM information_schema.Tables T INNER JOIN
	INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME
WHERE C.DATA_TYPE = 'bit'

Open in new window

0
 
FrancineTaylorAuthor Commented:
Hmm.  Still not working...

Invalid object name 'information_schema.Tables'

0
 
eridanixCommented:
This can be problem with your perminiton.
Try to login as administrator if you can.

I have no problem with both this queries.
0
 
Kevin CrossChief Technology OfficerCommented:
Why are you joining to anything? INFORMATION_SCHEMA.COLUMNS has the information, right?
SELECT C.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.DATA_TYPE = 'bit'

Open in new window

0
 
eridanixCommented:
@mwvisa1: Thanks for info, you are right. In my projects I use the right case format. But I'am also accustomed, that most of databases I used in past was case insensitive.
0
All Courses

From novice to tech pro — start learning today.