Search for columns in tables that are a specific data type?

[James R.]

Hi,

First off I want to start by saying this is an Access 97 question; I know
it's not MS supported and appreciate any help that can be given for this. I'm
working on converting this archaic database to SQL Server 2005; it's quite
the project.

Here is my current problem. I used SSMA for Access some time ago to setup
the initial data structure. Now one thing that didn't get migrated properly
was the AutoNumber data type; it did get migrated as an Integer, but it
didn't get marked as IDENTITY or a seed value placed in for it. So, I'll have
to make this change manually.

The problem is that my database conversion has approximately 180+ tables,
and some of those tables have 30+ fields.

I want to know if there is a Query I can run on the Access 97 database to
list all of the fields (and the table the field is in) that are of the
AutoNumber data type? I know how to do this in SQL but can't seem to find any
correlating system tables that contain this information.

I don't want to have any changes made to the data type or anything...I just
want a simple listing of the fields/tables that have the data type of
AutoNumber so I can simply go into the SQL version and make the proper field
Identity Specific.

If it's of any help the below is a query I've used in SQL to find a BIT data
type for all the tables in the database; this is the same results (or idea)
that I want to accomplish on the Access 97 database. Hopefully this can be
done!

DECLARE @OldDT nvarchar(3)
SET @OldDT = 'bit'
SELECT
o.Name AS Table_Name,
c.Name AS Column_Name
FROM
sys.Objects o JOIN sys.Columns c ON c.Object_id = bject_ID
WHERE
o.Type IN ('U')
AND type_name(c.system_type_id) = @OldDT
GROUP BY o.name, c.name;

I appreciate any help that can be provided!

Thanks,
James

 

[Douglas J. Steele]

No query will give you that information, but you can run some VBA to get it.

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
For Each fldCurr In tdfCurr.Fields
If (fldCurr.Attributes And dbAutoIncrField) <> 0 Then
Debug.Print "Field " & fldCurr.Name & " in table " & _
tdfCurr.Name & " is an AutoNumber field."
End If
Next fldCurr
Next tdfCurr
Set dbCurr = Nothing

That'll write the results to the Immediate window (Ctrl-G)

 

[James R.]

Doug,

Thank you; I will try this out. I've also been suggested to use the
Documenter to display the Field Types and Descriptions; which I then would
just do a Find to get the information.

Your idea may present a better format with less work; I like that! =)

Thanks,
James
--
Knowledge is the first step towards success. Little knowledge creates big
ideas.

No query will give you that information, but you can run some VBA to get it.

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
For Each fldCurr In tdfCurr.Fields
If (fldCurr.Attributes And dbAutoIncrField) <> 0 Then
Debug.Print "Field " & fldCurr.Name & " in table " & _
tdfCurr.Name & " is an AutoNumber field."
End If
Next fldCurr
Next tdfCurr
Set dbCurr = Nothing

That'll write the results to the Immediate window (Ctrl-G)

 

[zhen]

Hi,

First off I want to start by saying this is an Access 97 question; I know
it's not MS supported and appreciate any help that can be given for this. I'm
working on converting this archaic database to SQL Server 2005; it's quite
the project.

Here is my current problem. I used SSMA for Access some time ago to setup
the initial data structure. Now one thing that didn't get migrated properly
was the AutoNumber data type; it did get migrated as an Integer, but it
didn't get marked as IDENTITY or a seed value placed in for it. So, I'll have
to make this change manually.

The problem is that my database conversion has approximately 180+ tables,
and some of those tables have 30+ fields.

I want to know if there is a Query I can run on the Access 97 database to
list all of the fields (and the table the field is in) that are of the
AutoNumber data type? I know how to do this in SQL but can't seem to find any
correlating system tables that contain this information.

I don't want to have any changes made to the data type or anything...I just
want a simple listing of the fields/tables that have the data type of
AutoNumber so I can simply go into the SQL version and make the proper field
Identity Specific.

If it's of any help the below is a query I've used in SQL to find a BIT data
type for all the tables in the database; this is the same results (or idea)
that I want to accomplish on the Access 97 database. Hopefully this can be
done!

DECLARE @OldDT nvarchar(3)
SET @OldDT = 'bit'
SELECT
o.Name AS Table_Name,
c.Name AS Column_Name
FROM
sys.Objects o JOIN sys.Columns c ON c.Object_id = bject_ID
WHERE
o.Type IN ('U')
AND type_name(c.system_type_id) = @OldDT
GROUP BY o.name, c.name;

I appreciate any help that can be provided!

Thanks,
James