buscar nombre de columna en todas las tablas sql server 1

buscar nombre de columna en todas las tablas sql server

SELECT  sysobjects.name AS table_name, syscolumns.name AS column_name,
           systypes.name AS datatype, syscolumns.LENGTH AS LENGTH
FROM       sysobjects INNER JOIN
               syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
               systypes ON syscolumns.xtype = systypes.xtype
WHERE     (sysobjects.xtype = 'U') 
                and (UPPER(syscolumns.name) like upper('%myFieldName%'))
ORDER BY sysobjects.name, syscolumns.colid

Here is what the above code is Doing:
1. It’s looking for all tables that have a column name that contains the string ‘myFieldName’
2. It’s returning the table name, column name, data type, and length of the column
3. It’s ordering the results by table name and column id

You can change the query to suit your needs. For example, if you want to find all tables that have a column name that starts with ‘myFieldName’, you can change the where clause to:

WHERE (sysobjects.xtype = ‘U’)
and (UPPER(syscolumns.name) like upper(‘myFieldName%’))

If you want to find all tables that have a column name that ends with ‘myFieldName’, you can change the where clause to:

WHERE (sysobjects.xtype = ‘U’)
and (UPPER(syscolumns.name) like upper(‘%myFieldName’))

If you want to find all tables that have a column name that contains ‘myFieldName’ anywhere in the column name, you can change the where clause to:

WHERE (sysobjects.xtype = ‘U’)
and (UPPER(syscolumns.name) like upper(‘%myFieldName%’))

If you want to find all tables that have a column name that is exactly ‘myFieldName’, you can change the where clause to:

WHERE (sysobjects.xtype = ‘U’)
and (UPPER(syscolumns.name) = upper(‘myFieldName’))

If you want to find all tables that have a column name that is exactly ‘myFieldName’ and the data type is ‘varchar’, you can change the where clause to:

WHERE (sysobjects.xtype = ‘U’)
and (UPPER(syscolumns.name) = upper(‘myFieldName’))
and (UPPER(systypes.name) = upper(‘varchar’))

If you want to find all tables that have a column name that is exactly ‘myFieldName’ and the data type is ‘varchar’ and the length is ’50’, you can change the where clause to:

WHERE (sysobjects.xtype = ‘U’)
and (UPPER(syscolumns.name) = upper(‘myFieldName’))
and (UPPER(systypes.name) = upper(‘varchar’))
and (syscolumns.LENGTH = 50)

If you want to find all tables that have a column name that is exactly ‘myFieldName’ and the data type is ‘varchar’ and the length is ’50’ and the table name is ‘myTableName’, you can change the where clause to:

WHERE (sysobjects.xtype = ‘U’)
and (UPPER(syscolumns.name) = upper(‘myFieldName’))
and (UPPER(systypes.name) = upper(‘varchar’))
and (syscolumns.LENGTH = 50)
and (UPPER(sysobjects.name) = upper(‘myTableName’))

If you want to find all tables that have a column name that is exactly ‘myFieldName’ and the data type is ‘varchar’ and the length is ’50’ and the table name is ‘myTableName’ and the column id is ‘1’, you can change the where clause to:

WHERE

Similar Posts