Find Tables With No PK

Jan 27, 2004

I modified my origianl script (used a cursor) to use a looping method I saw in a different thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30286 by Nigel Rivett. Since I used code on this board I though I would post back for the greater SQL Team script toolbox. Feel free to use it to catch those deliquent sql programmers who forget to create a PK. Enjoy.



declare @database varchar(128) ,
@maxdatabase varchar(128) ,
@cmd nvarchar(1000)

create table #pk (databasename sysname, tablename sysname)

select @database = '', @maxdatabase = max(name) from master.dbo.sysdatabases where dbid > 5
while @database < @maxdatabase
begin
select @database = min(name) from master.dbo.sysdatabases where dbid > 5 and name > @database

set @cmd = 'select ''' + @database + ''', o.name from ' + @database + '.dbo.sysobjects o join ' + @database + '.dbo.sysindexes i on o.id=i.id
where o.xtype=''U'' and i.indid = 0'

insert into #pk exec sp_executesql @cmd
end

select * from #pk

drop table #pk


- Eric

View 10 Replies


ADVERTISEMENT

Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?

Jul 26, 2005

I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you

View 4 Replies View Related

Find A Key In All Tables

Oct 28, 2013

I have many tables that have as one of its keys POLICY_NUMBER is there a way to write a script that will look in all tables in a db for a certain POLICY_NUMBER

View 5 Replies View Related

Find Available Tables In A DB

Mar 20, 2008

I am brand new to using SQL. I created a DB with a bunch of tables there. What I would like to do is from my VB.NET application I would like to allow the user to see what tables are available in that DB.



Also I need a way to check if a table exists. I used the following:

strTemp = "IF EXISTS " & _
"(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'simplesql') AND type in (N'U'))"
cmd.Connection = conncmd.CommandType = CommandType.Textcmdcmd.ExecuteNonQuery()

but I get an exception : Incorrect syntax near ')'

thanks for your help

View 4 Replies View Related

How To Find All The Tables Which Are In Database

Feb 24, 2008

 Hi......i want to find all the tables which are in database. is there any hint or code that can help meThanks in advance

View 4 Replies View Related

Find Tables With Column Name

Oct 16, 2006

How can I find all the tables with a specific column name?

View 3 Replies View Related

How To Find Out Tables Which Can't Be Replicated

Jun 22, 2004

Is there any query to find out all the tables without a Primary key or without a Unique index ?

View 3 Replies View Related

Find Same Coumn In Other Tables

Jun 9, 2008

Is there any way i can find some certain fields in the whole database? like i know a column called Type_ID in table Sys_Type, how can i know there are same Type_ID column in other tables? Thanks in advance.

View 2 Replies View Related

Find All Differences Between 2 Tables

Mar 5, 2015

I have this 2 tables:

Table1: Table2:

ID Value ID Value
--------- ---------
1 2 2 1
2 1 3 4
4 3 4 4I

need to find all differences between the 2 Tables.

A mismatch can be:

- Table1.ID exists, Table2.ID does not exist
- Table2.ID exists, Table1.ID does not exist
- Table1.ID = Table2.ID and Table1.Value <> Table2.Value

So I want to be returned:

ID Value ID Value
--------------------
1 2 NULL NULL
4 3 4 4
NULL NULL 3 4

There must be something simpler than the solution I came up with which is:select * from Table1 a full outer join Table2 b on a.Id = b.Id

WHERE a.value <> b.value or (a.value IS NULL and b.value IS NOT NULL) or (a.value IS NOT NULL and b.value IS NULL)Martin

View 4 Replies View Related

How To Find The Tables In A View

Nov 15, 2006

Dear friends,
is there any way to find the table names from a view?

ex: suppose i have a view named as vorganization.i need all the tables in this view. is it possible to get through a query? same case for procedures and functions also.........

or we have to use sp_helptext?


is there any better way?

thank you very much.

Vinod

View 12 Replies View Related

How To Find No. Of Databases And Tables?

Mar 26, 2008

Hi,
is there any way to find out the no. of databases and
no. of tables in a particular database using a sql query ???

If any server variable is used then please mention that???


thanx in advance

San

View 20 Replies View Related

How Can I Find Connection Between Two Tables???

Mar 19, 2008

Hi,

I have a database with 150 tables.
I need to find connections between specified tables. (I need to find through what fileds and tables are table1 and table2 connected)
Is there any tool in SQL Server for this?

Thanx

View 1 Replies View Related

How To Compare Two Tables And Find The Gap?

Mar 18, 2008



Hi,

We have inventory table which is updated everyday.
I need to find the gap between updated table vs history (which means yesterday's data).
what i did is I just copy the inventory everyday before it is updated.
So, whenver inventory table is updated I want to know the gap between these two (history vs updated) tables.

The structure of two tables are same and here are columns:
date_key (fk)
store_key(fk)
item_key(fk)
begin_inventory_dollars
ending_inventory_dollars
begin_inventory_units
ending_inventory_units

I want to compare each row and if the value is changed I want to have a gap (updated value - history value) and if data is new then just want to add.

Here is tsql: however when I compare to gap between table from excel spreadsheet and this query, this query does not return a right value. (Some are correct but some don't)




Code Snippet
select a.date_date_key,
sum(a.Beg_Inventory_Dollars- (case when b.beg_inventory_dollars is NULL then 0 else b.Beg_Inventory_Dollars end)),

sum(a.Beg_Inventory_Units-(case when b.beg_Inventory_Units is NULL then 0 else b.beg_Inventory_Units end)) ,

sum(a.Ending_Inventory_Dollars- (case when b.Ending_Inventory_Dollars is NULL then 0 else b.Ending_Inventory_Dollars end)),

Sum(a.Ending_Inventory_Units-(case when b.Ending_Inventory_units is NULL then 0 else b.Ending_Inventory_Units end))

from UPDATED TABLE a

left outer join HISTORY TABLE b

on b.item_key = a.item_key and

b.store_key =a.store_key and

b.date_key =a.date_key

group by a.fisc_date_key







Did I miss something in here?
Any kind of advice would be grealy appreciated.
Thanks.

View 4 Replies View Related

Find Max Serial Number From 2 Tables

Feb 11, 2008

I need a query to find max serial number by comparing two different tables. Here is my requirementI am having two tables named Table1 and Table2. Each tables having more than 30,000,000 records.I want a simple query to find Max srno from two tables.For exampleIf Table1 max is 245 where partno=2 and ano=2and Table2 max is 343 where partno=2 and ano=2Then 343 is max serial noIf Table1 max is 435 where partno=2 and ano=2and Table2 max is 34 where partno=2 and ano=2Then 435 is max serial noI used this query but its taking more time  select max(v.MaxSrNo) from ((select max(MaxSrNo) as MaxSrNo from Table1 where partno=@partno and ano=@ano)union all (select max(MaxSrNo) from Table2 where partno=@partno and ano=@ano)) as v Pls give me a simple query to find max srno.

View 3 Replies View Related

How To Find Text/data In All Of My Tables?

Jan 18, 2000

Hello,

I have a quick question. Can you please tell me how to find/search a string/value in all of my tables in a database.

For example I need to find a value/data "GH45678", where-ever it appears in a database.

I would really appreciate your prompt response. I would also like to thank you very much in advance for your time/help.

Note: I need to find specific data in all of my tables, I am looking for something like text search in my database. I am using SQL Server 6.5. Thanks

View 1 Replies View Related

How To Find Out All The Statistics From All The Tables And Drop Them

Jul 21, 2003

How to find out all the statistics from all the tables and drop them..any script anyone can help with?
When we are trying to make datatype changes in few related tables,it's giving error saying that some statistics are dependent onthe column blah blah...

Thanks,
Sheila.

View 1 Replies View Related

How To Find Maximum Number From Two Tables

Feb 19, 2012

My db contains two table Employee_detail and Student_Detail.Employee_detail has Emp_id and Student_Detail has stud_id.Now my problem is that i want to get one maximum no from these two table.

View 11 Replies View Related

Compare Two Tables And Find The Occurance

May 17, 2008

Hi friends,

I have a two table with following fields, table names are tbl_userinfo, tbl_Property.

tbl_userinfo fields are

user_id name
1 dhin
2 Mike
3 sam
4 Red


tbl_Property fields are

prpty_Id User_Id Address
1 1 3CostalRoad
2 1 westbengal
3 2 Loasass

what i want to do is, if tbl_info User_id occures in tbl_property, i want to display that full info abt tbl_userinfo

after comparing two tables Expected result is
user_id name
1 dhin
2 Mike


Please help me how to do this

View 6 Replies View Related

Find The List Of Tables For A User?

Feb 18, 2015

how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.

View 1 Replies View Related

Where To Find Info On The Concept Of SQL Tables

Feb 7, 2006

i would like to know where can i find information on the concept of SQL like Base Table , View Table and Search Table as now i am using VS.Net 2005 and SQL Server 2005 to write a addressbook program and i am just a newbie to Sql and c#... i wish to write my sql commands in c# side before passing it into sql and may i know how to do it?

View 20 Replies View Related

Find Common Value In Column For 30 Tables

Mar 21, 2006

Hi,

I am trying to query for a common value in a column called "file_auth_nbr" in 30 different tables. I was going to try something like this (see below) but wasn't sure if this was the most efficient, fastest, or correct, way to get what I'm looking for:

Select distinct a.file_auth_nbr from table1 as a
join table2 as b
on a.file_auth_nbr = b.file_auth_nbr
join table3 as c
on a.file_auth_nbr = c.file_auth_nbr
join table4 as d
on a.file_auth_nbr = d.file_auth_nbr
join table5 as e
on a.file_auth_nbr = e.file_auth_nbr
......etc., etc.

Any suggestions would be much appreciated,
Jeff

View 1 Replies View Related

How Can I Find What Tables Are Loaded In Memory ?

Jul 23, 2005

I'm doing some performance reviews and wish to know what tables SQL haspinned in memory and which ones have are loaded through usage ...Is there a way ?Thanks,Craig

View 3 Replies View Related

Find Similar Strings In Two Tables

Jul 23, 2005

I have two tables in the same SQL database. Both have a similar numericfield. One field has the same number as the other field but is prefixedwith various letters. I know how to use LIKE but won't know the partialstring I am looking for. I am trying to use LIKE '%' + Field A orsomething that will do this.Eg.Table 1 Field A is 'A12345"Table 2 Field B is '12345"I want to find every record in Table 1 Field A that contains the exactField B data.*** Sent via Developersdex http://www.developersdex.com ***

View 3 Replies View Related

Find All Tables That A Table Is Related To

Sep 8, 2006

how can i find all the names of tables that a specific table is related to in tsql?

View 3 Replies View Related

How To Find The Last Updated Tables In A Database

May 28, 2007

Hi,



I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ??

View 1 Replies View Related

To Find Out Empty Tables In A Database

Jul 20, 2006

Hi all,

I have a database which gets its daily feed from a ftp file. Now is there any way i can figure out the empty tables in the databese which doesnot get any data on the feed.

Thanks in advance,

View 10 Replies View Related

How Can One Find Out If Two Tables Can Be Joinned Dynamically

Jul 25, 2006

Hi All,

Given a table, is there a way to programmatically find out what other tables and views it can be joinned with? Essentially, given two tables, two views, or a table and a view, I want to be able to find out if they can be joinned. I was able to find related tables with sp_fkeys, but this does not work all the time, and does not work at all with views. I also played with the information_schema but got mixed up and confused quickly.

Any help would be greatly appreciated.

View 10 Replies View Related

How To Find Out Missing Columns Between Tables

Apr 21, 2015

We are trying to find out the difference between tables in CUSTOMER database and CUSTOMER_coded database. The goal is to find out if there are any columns missing in each table of CUSTOMER_coded database.

We need the list of tables in CUSTOMER_coded database that misses some column compare to its peer in CUSTOMER database (list of columns being missing also).

I googled, but I get only all the columns in tables of database.

I need missing columns of all the tables when we compare these 2 databases( CUSTOMER and CUSTOMER_coded  databases).

View 8 Replies View Related

Find Out What Tables Contain A Specific Column

Jul 28, 2006

I want to write SQL that will search the tables in a database for a specific column, like this. For instance, I have a column "Unique_ID" that is in many of our tables (hundreds) but not in others and want to find out the tables it is in. It is always the first column.

I tried to find a system stored procdure to do this but couldn't and tried to create a script using the sysobjects and syscolumns tables in the Master db, but came to a roadblock because they don't seem to be related at all.

I would surely appreciate if someone else has already done this!

Thanks!

View 6 Replies View Related

How To Find If Index Key Is ASC Or DESC From System Tables?

Apr 29, 2003

There is a index: CustomerInfo_1
with keys: customerId, EnteryDate DESC
I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?
I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.
Any help in this regard will be truly appreciated.

Thank you.
Regards,
Anuj Goyal

View 4 Replies View Related

How To Find If Index Key Is ASC Or DESC From System Tables?

Apr 29, 2003

There is a index: CustomerInfo_1
with keys: customerId, EnteryDate DESC
I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?
I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.
Any help in this regard will be truly appreciated.

Thank you.
Regards,
Anuj Goyal

View 2 Replies View Related

How To Find Rowcount And Size(space Used) For All Tables In A Db?

Jun 2, 2004

How to find rowcount and size(space used) for all tables in a db? Can any one give me the script please?

Thanks,

View 2 Replies View Related

Script: Find Tables Without Primary Keys

Aug 3, 2002

...got tired of looking at them by hand.

Cheers
-b

DECLARE @vcDB varchar(20),@vcSchema varchar(20),@vcTable varchar(200)

Select @vcDB='mydb',@vcSchema='dbo'

DECLARE cLoop cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_CATALOG=@vcDB
and TABLE_SCHEMA=@vcSchema
order by TABLE_NAME ASC

open cLoop

FETCH NEXT FROM cLoop INTO @vcTable
WHILE @@FETCH_STATUS=0
BEGIN
if not exists (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = @vcSchema
AND TABLE_NAME = @vcTable
AND CONSTRAINT_TYPE = 'PRIMARY KEY')
print @vcTable + ' does not have a primary key'

FETCH NEXT FROM cLoop INTO @vcTable
END
Close cLoop
DEALLOCATE cLoop

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved