Is there a way to search the database for a certain datatype?
-
I was wondering if there is a stored procedure or way to query the database to show all the tables and columns based upon a datatype. Such as if I wanted to see what tables have nvarchar types.
Thanks
-
Look into the sysobjects (type='U'), syscolumns and systypes tables in BOL or run a few queries and you'll get the hang of it.
There's also: select * from Information_Schema.Columns
-
If you want to write your own queries you will also need to check out the SysTypes table.
Good luck
. -
That's what happens when you read too fast... Dave had already mentionned the systypes table... obviously.
Good thing that the week is almost over
. -
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY TBLNAME,COLNAME
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I would like to extend this code to change the datatype for a column common to all tables in my database to tinyint. The column exists on each table with different datatypes, numeric, integer, money, floating, etc.
How can I write my UPDATE statement to include the JOIN clause to reference both SYSOBJECTS and SYSCOLUMNS tables?
-
Script below will list out table level details for all databases except tempdb.
execute master..sp_MSforeachdb
@command1 ='select ''?'' as "DatabaseName",d.name as "OwnerName",c.name as "TableName",a.name as "ColumnName",b.name as "DataType",a.prec as "Precision",
a.scale as "Scale",a.collation as "Collation"
from ?..syscolumns a,?..systypes b,?..sysobjects c, ?..sysusers d
where a.xtype = b.xtype and a.id = c.id and c.xtype = ''U''
and d.uid = c.uid
and ''?'' not like ''tempdb'' order by TableName,ColumnName'
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
This should do it:
select * from information_schema.columns where DATA_TYPE = 'nvarchar'
-
UPDATE statment? or do you mean ALTER statement? something like this would give you the statements to generate statments that had columns of type float to money, for example:
--select * from systypes reveals all data types
Results:
('image','text','uniqueidentifier','tinyint','smallint','int','smalldatetime','real','money','datetime','float','sql_variant','ntext','bit','decimal','numeric','smallmoney','bigint','varbinary','varchar','binary','char','timestamp','nvarchar','nchar','xml','sysname')
select object_name(id)As TableName,
name as ColumnName,
'ALTER TABLE ' + object_name(id) + ' ALTER COLUMN ' + name + ' money' as DefaultChangeStatment
from syscolumns where type_name(xtype)='float'
results:
SFFEE CALCPERCENTAGE ALTER TABLE SFFEE ALTER COLUMN CALCPERCENTAGE money
TBARMDET MARGIN ALTER TABLE TBARMDET ALTER COLUMN MARGIN money
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
You cannot just change the type in syscolumns. Even though you may only store integer data in a varchar column, the data is not stored the same way in the database. You have to do an ALTER statement as Lowell pointed out.
Brian
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply