February 20, 2014 at 12:14 pm
Is there a way to query all the columns in a database that have the data type as Integer
February 20, 2014 at 12:17 pm
Dieselbf2 (2/20/2014)
Is there a way to query all the columns in a database that have the data type as Integer
Can you provide some insight as to what you want? Of course there is a way to query all the columns, put them as a column in a select statement. I have a feeling that is not exactly what you are looking for. Also, when you say Integer do you really only want int columns? What about bigint, tinyint, smallint? What about numeric types that function as an int? Things like numeric(9,0).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2014 at 12:21 pm
Sean Lange (2/20/2014)
Dieselbf2 (2/20/2014)
Is there a way to query all the columns in a database that have the data type as IntegerCan you provide some insight as to what you want? Of course there is a way to query all the columns, put them as a column in a select statement. I have a feeling that is not exactly what you are looking for. Also, when you say Integer do you really only want int columns? What about bigint, tinyint, smallint? What about numeric types that function as an int? Things like numeric(9,0).
Yes I want all columns in the database that have the data type as Int. I know how to find all column names in a database just not data type. Though something like this:
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE column is INT
ORDER BY schema_name, table_name;
February 20, 2014 at 12:25 pm
Have you checked the catalog views sys.tables and sys.columns?
Here is an example without taking into consideration user types.
SELECT
t.name AS tn,
sc.*
FROM
sys.tables AS t
INNER JOIN
sys.columns AS sc
ON sc.object_id = t.object_id
INNER JOIN
sys.types AS st
ON st.system_type_id = sc.system_type_id
WHERE
t.type_desc = 'USER_TABLE'
AND st.name IN ('tinyint', 'smallint', 'int', 'bigint');
February 20, 2014 at 12:32 pm
hunchback (2/20/2014)
Have you checked the catalog views sys.tables and sys.columns?Here is an example without taking into consideration user types.
SELECT
t.name AS tn,
sc.*
FROM
sys.tables AS t
INNER JOIN
sys.columns AS sc
ON sc.object_id = t.object_id
INNER JOIN
sys.types AS st
ON st.system_type_id = sc.system_type_id
WHERE
t.type_desc = 'USER_TABLE'
AND st.name IN ('tinyint', 'smallint', 'int', 'bigint');
How and where can I learn this. I am new to the writing code as you can see.
Thank you Sir.
February 20, 2014 at 12:36 pm
Dieselbf2 (2/20/2014)
hunchback (2/20/2014)
Have you checked the catalog views sys.tables and sys.columns?Here is an example without taking into consideration user types.
SELECT
t.name AS tn,
sc.*
FROM
sys.tables AS t
INNER JOIN
sys.columns AS sc
ON sc.object_id = t.object_id
INNER JOIN
sys.types AS st
ON st.system_type_id = sc.system_type_id
WHERE
t.type_desc = 'USER_TABLE'
AND st.name IN ('tinyint', 'smallint', 'int', 'bigint');
How and where can I learn this. I am new to the writing code as you can see.
Thank you Sir.
I think you just did learn this. 😉
The forums around here are a great place to learn this and many other tidbits of information.
Post lots of questions, soak up the responses like a sponge. Start finding threads where you feel comfortable helping. Pretty soon you will find yourself helping other more than you need help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2014 at 12:45 pm
With the Information_Schema views, this kind of queries become easier.
DECLARE @sql VARCHAR(MAX)
SELECT @sql = (SELECT 'SELECT ' +
STUFF( (SELECT ',' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS x
WHERE DATA_TYPE = 'int'
AND x.TABLE_NAME = c.TABLE_NAME
AND x.TABLE_SCHEMA = c.TABLE_SCHEMA
FOR XML PATH('')), 1, 1, '')
+ ' FROM ' + QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME) + ';' + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE DATA_TYPE = 'int'
GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME
FOR XML PATH(''))
EXEC( @sql)
EDIT: Added a WHERE clause to the subquery and added the code to execute it automatically
I might have misunderstood the problem 😀
February 20, 2014 at 12:48 pm
To add to Sean's response, see if you can get a system views map for whatever version you are using. There is a lot you can learn from there mainly the relationship among all these tables.
http://www.microsoft.com/en-us/download/confirmation.aspx?id=39083
February 20, 2014 at 12:50 pm
hunchback (2/20/2014)
Have you checked the catalog views sys.tables and sys.columns?Here is an example without taking into consideration user types.
SELECT
t.name AS tn,
sc.*
FROM
sys.tables AS t
INNER JOIN
sys.columns AS sc
ON sc.object_id = t.object_id
INNER JOIN
sys.types AS st
ON st.system_type_id = sc.system_type_id
WHERE
t.type_desc = 'USER_TABLE'
AND st.name IN ('tinyint', 'smallint', 'int', 'bigint');
I actually came out with the same info as well:
Select Table_Schema + + '.' + table_Name As 'Sch.TableName', Column_Name, DATA_TYPE
From information_schema.columns
Where DATA_TYPE = 'int'
Order By 'Sch.TableName', column_Name
I just had to look up the table information_schema.columns.
Thanks
February 20, 2014 at 12:53 pm
hunchback (2/20/2014)
To add to Sean's response, see if you can get a system views map for whatever version you are using. There is a lot you can learn from there mainly the relationship among all these tables.http://www.microsoft.com/en-us/download/confirmation.aspx?id=39083
Thank you
February 20, 2014 at 12:57 pm
Dieselbf2 (2/20/2014)
hunchback (2/20/2014)
Have you checked the catalog views sys.tables and sys.columns?Here is an example without taking into consideration user types.
SELECT
t.name AS tn,
sc.*
FROM
sys.tables AS t
INNER JOIN
sys.columns AS sc
ON sc.object_id = t.object_id
INNER JOIN
sys.types AS st
ON st.system_type_id = sc.system_type_id
WHERE
t.type_desc = 'USER_TABLE'
AND st.name IN ('tinyint', 'smallint', 'int', 'bigint');
How and where can I learn this. I am new to the writing code as you can see.
Thank you Sir.
EXCELLENT Question! The answer is "Books Online", which is the "Help" system that comes with SQL Server. When in SSMS, press the [f1} key to get there. My recommendation for your first "lookups" for study purposes.
Functions
sys.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2014 at 1:55 pm
+1 for Books Online. MUCH more efficient (for all concerned) method of quickly learning about SQL Server and TSQL!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply