Get all table names with a given column name

All, is there a way of getting all table names that contain a column
name?
I'm looking at a DB that has 125+ tables and I'm interested in finding
all table names that contain the column order_date.
How can I do it?
TIA,
SashiSelect table_name from information_Schema.columns where
column_name='column name'

Madhivanan

Sashi wrote:

Quote:

Originally Posted by

All, is there a way of getting all table names that contain a column
name?
I'm looking at a DB that has 125+ tables and I'm interested in finding
all table names that contain the column order_date.
How can I do it?
TIA,
Sashi

|||How can I do it?

One method is to query the INFORMATION_SCHEMA views:

SELECT
c.TABLE_SCHEMA,
c.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON
t.TABLE_SCHEMA = c.TABLE_SCHEMA AND
t.TABLE_NAME = c.TABLE_NAME
WHERE
t.TABLE_TYPE = 'BASE TABLE' AND
c.COLUMN_NAME = 'column order_date'
ORDER BY
c.TABLE_SCHEMA,
c.TABLE_NAME

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sashi" <smalladi@.gmail.comwrote in message
news:1152627046.364476.199010@.h48g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

All, is there a way of getting all table names that contain a column
name?
I'm looking at a DB that has 125+ tables and I'm interested in finding
all table names that contain the column order_date.
How can I do it?
TIA,
Sashi
>

Generic Stored Procedure

I have 24 lookup tables that contain the same columns (e.g. Rec_Id (identity field), Code, Desc) and I need to build an interface for each of these tables with Create, Update, and Delete capabilities.

I would like to create three stored procedures (create, update, delete) that would perform the task for any of the lookup tables.

For example, table AGENCY has three fields (Agency_id (identity field), Agency_Code, Agency_Desc) and I need to add a new record to AGENCY. I would like to call a stored procedure that would take the parameters @.tablename, @.code, @.desc, and then create a new record in the table specified by @.tablename.

I could then use a single "create" stored procedure for all of the lookup tables when adding a new record, rather than creating a separate "create" stored proc for each of my lookup tables.

Any help would be greatly appreciated!::I would like to create three stored procedures (create, update, delete) that would perform
::the task for any of the lookup tables.

Forget it. Not a feasible way.|||Thanks for your input!

I'm currently writing separate stored procs for each of my lookup tables and will continue to do so until somebody shows me a better way.|||Skip the SP's, go with dynamic SQL and use an intelligent DAL to never maintain the SQL anymore.|||I've written generic stored procedures in the past. The trick is to use the EVAL function of SQL. As an example, here is a stored procedure that would take a table name as parameter and return all records from this table.

The stored procedure is defined as follow:
CREATE PROCEDURE SP_Query
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

GO

You must call it as follow:
SP_Query 'Customers'

As your tables have the same columns it should be easy to built your 3 generic stored procedures in the same way as my example.

Good luck,
Olivier Giulieri
www.kakoo.net|||You can use a mixture of dynamic as static.
Have a utility the writes stored proces and either map them or construct their name.

So...
Template:
Update<table>
Gives:
UpdateMyTable1
UpdateMyTable2
...etc

Then the dynamic part is constructing the name of the stored proc...
CommandText = "Update" + strTableName

PS Don't prefix stored procs with "SP_" it's bad news.|||Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').|||I was just making a quick example of stored procedure to illustrate the use of "EVAL". For sure, I picked a very bad name. Here is my example again:

CREATE PROCEDURE GenericQuery
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

GO

PS: Am I accused of not reading documentations because you said "Forget it. Not a feasible way." and my solution may work? Please let's just try to be constructive here.|||*If* you do need to use an "EVAL" function then consider using sp_executeSQL instead. It's geared up for param' queries.|||<Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').
Easy now big fella! ;)

Check every website that has examples, I will bet 99% use sp_ . For the record I do not, but if you take samples from the net, or even Microsoft you will see they almost always use sp_ so you cannot get too uptight at people for following the convention, even if it does reduce processing efficiency.|||There is no 'good' way to do this.

If you use a new proc for each it's a lot of writing and a lot of changing if it changes. If you use dynamic stored procs you lose the security of refusing anyone direct access to your tables (dynamic stored procs require the use to have table access) and they run much more slowly.

My work around when this happens,

Write one proc per table.
Write one proc which the application calls, it then calls the correct proc. That way your application programmers only need to remember one stored procedure to call and you do the rest.

Avoid using IF statements in an SQL proc.

If you have something like this:

IF X
 Begin
 {code code code}
 End
Else
 Begin
 {code code code}
 End

When you execute only half of the proc will be in the plan and run efficiently. The other half will be adhoc.

Consider this:

If X
 Begin
 exec prCode1
 End
Else
 Begin
 exec prCode2
 End

Since each If calls a procedure that has an execution plan it runs more efficiently (AKA faster).

</code>

Generating a flat file output from a select

I want to create a script file to executed from the command line. The script will contain a simple select, which depending on which database it is run against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when the script is executed from the command line it will produce a flat file with a list of the users on the database.
http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.
|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.

Generating a flat file output from a select

I want to create a script file to executed from the command line. The scrip
t will contain a simple select, which depending on which database it is run
against will produce a flat file with the output results.
e.g. a script file with a select such as 'SELECT name FROM sysusers'. when
the script is executed from the command line it will produce a flat file wit
h a list of the users on the database.http://www.aspfaq.com/2482
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.|||Hi,
You can use BCP OUT with QUERYOUT option. Inside
Query out you can give your TSQL to extract the data out.
Sample:-
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Tiarnan" <anonymous@.discussions.microsoft.com> wrote in message
news:736E002A-D0DC-4B49-A370-6FF2D203B53A@.microsoft.com...
> I want to create a script file to executed from the command line. The
script will contain a simple select, which depending on which database it is
run against will produce a flat file with the output results.
> e.g. a script file with a select such as 'SELECT name FROM sysusers'.
when the script is executed from the command line it will produce a flat
file with a list of the users on the database.

Generate SQLscript for db?

Due to the large size db, I need to generate a SQL script and keep it in
VSS. The script should contain all create objctes statemnts. Using
'Generate SQL Script' in the Enterprise Manager by right clicking the db
is not helping. What I am missing here?
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***> Using
> 'Generate SQL Script' in the Enterprise Manager by right clicking the db
> is not helping.
Why not? If you don't say what it does wrong, it is hard for us to help you.
Here's a general
article on the subject: http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message news:eclCR3rkFHA.1948@.TK2MSFTNGP12.p
hx.gbl...
> Due to the large size db, I need to generate a SQL script and keep it in
> VSS. The script should contain all create objctes statemnts. Using
> 'Generate SQL Script' in the Enterprise Manager by right clicking the db
> is not helping. What I am missing here?
> Thanks!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Could you describe why that isn't working?
Visual Studio.NET also has the Generate Script from the Server Explorer.
Clint Hill
H3O Software
http://www.h3osoftware.com
Test Test wrote:
> Due to the large size db, I need to generate a SQL script and keep it in
> VSS. The script should contain all create objctes statemnts. Using
> 'Generate SQL Script' in the Enterprise Manager by right clicking the db
> is not helping. What I am missing here?
> Thanks!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||The script got created but it was without create objects statements.
This is what I did - in the 'Generate SQL Script' window, I clicked on
'Formatting' tab, I noticed 'Generate create and drop objects' boxes are
checked by default (which I do want). Then, I clicked on 'Options' tab,
and checked the script db (and the ones I needed) and hit ok, it did
generate a script but without create objects. I tried many times but
with same results. It has logins, dbs settings and create db statements
only. where all other create objects (tables, views, stored procs) went
(even though they were seleceted)?
*** Sent via Developersdex http://www.examnotes.net ***|||Did you add the objects you want to include to the right list box in the lef
t-most tab?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message news:utOLieskFHA.1148@.TK2MSFTNGP12.p
hx.gbl...
> The script got created but it was without create objects statements.
> This is what I did - in the 'Generate SQL Script' window, I clicked on
> 'Formatting' tab, I noticed 'Generate create and drop objects' boxes are
> checked by default (which I do want). Then, I clicked on 'Options' tab,
> and checked the script db (and the ones I needed) and hit ok, it did
> generate a script but without create objects. I tried many times but
> with same results. It has logins, dbs settings and create db statements
> only. where all other create objects (tables, views, stored procs) went
> (even though they were seleceted)?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Got it. Thanks, Tobor!
You have to hit 'Show All' in the 'General' tab to get the objects
selection. By defualt, it was all greyed (thats why I was ignoring it).
My bad.
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***