|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Sys Tables for Datetime typeI am looking for a specific Datetime column and I don't know what table it
resides in. How can I query the systables looking for all those columns with Datetime type??? Thank You! Use the view information_schema.columns instead.
select table_schema, table_name, ordinal_position, column_name, data_type from information_schema.columns where data_type = 'datetime' and objectproperty(object_id(quotename(table_schema) + '.' + quotename(table_name)), 'IsUserTable') = 1 and objectproperty(object_id(quotename(table_schema) + '.' + quotename(table_name)), 'IsMSShipped') = 0 order by table_schema, table_name, ordinal_position AMB Show quote "wnfisba" wrote: > I am looking for a specific Datetime column and I don't know what table it > resides in. > > How can I query the systables looking for all those columns with Datetime > type??? > > Thank You! Just a couple of things,
(a) upper case (e.g. INFORMATION_SCHEMA.COLUMNS) should be used as opposed to lower case, so that code will work equally well on case sensitive collations. (b) I would probably use DATA_TYPE IN ('DATETIME', 'SMALLDATETIME') because, while not explicitly specified, the OP may not care if the column is SMALL(DATETIME), may not know which datetime type it actually is, or may not even know the difference. A Show quote "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message news:BEC6B9ED-970C-4C35-AB7B-0D8C14738734@microsoft.com... > Use the view information_schema.columns instead. > > select > table_schema, > table_name, > ordinal_position, > column_name, > data_type > from > information_schema.columns > where > data_type = 'datetime' > and objectproperty(object_id(quotename(table_schema) + '.' + > quotename(table_name)), 'IsUserTable') = 1 > and objectproperty(object_id(quotename(table_schema) + '.' + > quotename(table_name)), 'IsMSShipped') = 0 > order by > table_schema, > table_name, > ordinal_position > > > AMB > "wnfisba" wrote: > >> I am looking for a specific Datetime column and I don't know what table >> it >> resides in. >> >> How can I query the systables looking for all those columns with Datetime >> type??? >> >> Thank You! We don't have this view INFORMATION_SCHEMA. Do I have to build that view???
Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > Just a couple of things, > > (a) upper case (e.g. INFORMATION_SCHEMA.COLUMNS) should be used as opposed > to lower case, so that code will work equally well on case sensitive > collations. > > (b) I would probably use DATA_TYPE IN ('DATETIME', 'SMALLDATETIME') because, > while not explicitly specified, the OP may not care if the column is > SMALL(DATETIME), may not know which datetime type it actually is, or may not > even know the difference. > > A > > > > > "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message > news:BEC6B9ED-970C-4C35-AB7B-0D8C14738734@microsoft.com... > > Use the view information_schema.columns instead. > > > > select > > table_schema, > > table_name, > > ordinal_position, > > column_name, > > data_type > > from > > information_schema.columns > > where > > data_type = 'datetime' > > and objectproperty(object_id(quotename(table_schema) + '.' + > > quotename(table_name)), 'IsUserTable') = 1 > > and objectproperty(object_id(quotename(table_schema) + '.' + > > quotename(table_name)), 'IsMSShipped') = 0 > > order by > > table_schema, > > table_name, > > ordinal_position > > > > > > AMB > > "wnfisba" wrote: > > > >> I am looking for a specific Datetime column and I don't know what table > >> it > >> resides in. > >> > >> How can I query the systables looking for all those columns with Datetime > >> type??? > >> > >> Thank You! > > > Can you load QA and execute "select @@version" and post the result.
AMB Show quote "wnfisba" wrote: > We don't have this view INFORMATION_SCHEMA. Do I have to build that view??? > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > Just a couple of things, > > > > (a) upper case (e.g. INFORMATION_SCHEMA.COLUMNS) should be used as opposed > > to lower case, so that code will work equally well on case sensitive > > collations. > > > > (b) I would probably use DATA_TYPE IN ('DATETIME', 'SMALLDATETIME') because, > > while not explicitly specified, the OP may not care if the column is > > SMALL(DATETIME), may not know which datetime type it actually is, or may not > > even know the difference. > > > > A > > > > > > > > > > "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message > > news:BEC6B9ED-970C-4C35-AB7B-0D8C14738734@microsoft.com... > > > Use the view information_schema.columns instead. > > > > > > select > > > table_schema, > > > table_name, > > > ordinal_position, > > > column_name, > > > data_type > > > from > > > information_schema.columns > > > where > > > data_type = 'datetime' > > > and objectproperty(object_id(quotename(table_schema) + '.' + > > > quotename(table_name)), 'IsUserTable') = 1 > > > and objectproperty(object_id(quotename(table_schema) + '.' + > > > quotename(table_name)), 'IsMSShipped') = 0 > > > order by > > > table_schema, > > > table_name, > > > ordinal_position > > > > > > > > > AMB > > > "wnfisba" wrote: > > > > > >> I am looking for a specific Datetime column and I don't know what table > > >> it > > >> resides in. > > >> > > >> How can I query the systables looking for all those columns with Datetime > > >> type??? > > >> > > >> Thank You! > > > > > > We don't have this view INFORMATION_SCHEMA. Do I have to build that view???
Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > Just a couple of things, > > (a) upper case (e.g. INFORMATION_SCHEMA.COLUMNS) should be used as opposed > to lower case, so that code will work equally well on case sensitive > collations. > > (b) I would probably use DATA_TYPE IN ('DATETIME', 'SMALLDATETIME') because, > while not explicitly specified, the OP may not care if the column is > SMALL(DATETIME), may not know which datetime type it actually is, or may not > even know the difference. > > A > > > > > "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message > news:BEC6B9ED-970C-4C35-AB7B-0D8C14738734@microsoft.com... > > Use the view information_schema.columns instead. > > > > select > > table_schema, > > table_name, > > ordinal_position, > > column_name, > > data_type > > from > > information_schema.columns > > where > > data_type = 'datetime' > > and objectproperty(object_id(quotename(table_schema) + '.' + > > quotename(table_name)), 'IsUserTable') = 1 > > and objectproperty(object_id(quotename(table_schema) + '.' + > > quotename(table_name)), 'IsMSShipped') = 0 > > order by > > table_schema, > > table_name, > > ordinal_position > > > > > > AMB > > "wnfisba" wrote: > > > >> I am looking for a specific Datetime column and I don't know what table > >> it > >> resides in. > >> > >> How can I query the systables looking for all those columns with Datetime > >> type??? > >> > >> Thank You! > > > Aaron,
Thanks for the comment. AMB Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > Just a couple of things, > > (a) upper case (e.g. INFORMATION_SCHEMA.COLUMNS) should be used as opposed > to lower case, so that code will work equally well on case sensitive > collations. > > (b) I would probably use DATA_TYPE IN ('DATETIME', 'SMALLDATETIME') because, > while not explicitly specified, the OP may not care if the column is > SMALL(DATETIME), may not know which datetime type it actually is, or may not > even know the difference. > > A > > > > > "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message > news:BEC6B9ED-970C-4C35-AB7B-0D8C14738734@microsoft.com... > > Use the view information_schema.columns instead. > > > > select > > table_schema, > > table_name, > > ordinal_position, > > column_name, > > data_type > > from > > information_schema.columns > > where > > data_type = 'datetime' > > and objectproperty(object_id(quotename(table_schema) + '.' + > > quotename(table_name)), 'IsUserTable') = 1 > > and objectproperty(object_id(quotename(table_schema) + '.' + > > quotename(table_name)), 'IsMSShipped') = 0 > > order by > > table_schema, > > table_name, > > ordinal_position > > > > > > AMB > > "wnfisba" wrote: > > > >> I am looking for a specific Datetime column and I don't know what table > >> it > >> resides in. > >> > >> How can I query the systables looking for all those columns with Datetime > >> type??? > >> > >> Thank You! > > > It did not return any rows???
Show quote "Alejandro Mesa" wrote: > Use the view information_schema.columns instead. > > select > table_schema, > table_name, > ordinal_position, > column_name, > data_type > from > information_schema.columns > where > data_type = 'datetime' > and objectproperty(object_id(quotename(table_schema) + '.' + > quotename(table_name)), 'IsUserTable') = 1 > and objectproperty(object_id(quotename(table_schema) + '.' + > quotename(table_name)), 'IsMSShipped') = 0 > order by > table_schema, > table_name, > ordinal_position > > > AMB > "wnfisba" wrote: > > > I am looking for a specific Datetime column and I don't know what table it > > resides in. > > > > How can I query the systables looking for all those columns with Datetime > > type??? > > > > Thank You! Can you post the statement you are suing?
AMB Show quote "wnfisba" wrote: > It did not return any rows??? > > > > "Alejandro Mesa" wrote: > > > Use the view information_schema.columns instead. > > > > select > > table_schema, > > table_name, > > ordinal_position, > > column_name, > > data_type > > from > > information_schema.columns > > where > > data_type = 'datetime' > > and objectproperty(object_id(quotename(table_schema) + '.' + > > quotename(table_name)), 'IsUserTable') = 1 > > and objectproperty(object_id(quotename(table_schema) + '.' + > > quotename(table_name)), 'IsMSShipped') = 0 > > order by > > table_schema, > > table_name, > > ordinal_position > > > > > > AMB > > "wnfisba" wrote: > > > > > I am looking for a specific Datetime column and I don't know what table it > > > resides in. > > > > > > How can I query the systables looking for all those columns with Datetime > > > type??? > > > > > > Thank You!  
Other interesting topics
|
|||||||||||||||||||||||
