I have 4 particular columns (crt_dt,upd_dt,entity_active and user_idn) in many of the tables in my database. Now i have to find all the tables having four columns mentioned above and cases are
1) if the column is nullable., then it should result 'Y'
2) if the column is not nullable., then it should result 'N'
3)if column is not present., then it should display '-'
Hi,I've enherited a big mess, a SQL Server 2000 database withapproximately 50 user tables and 65+ GB data, no explicitrelationships among entities (RI constraints whatsover), attempt tocreate an ERD would more than likely kill the relatively complexedapp, the owner would want to drop out of window, so,I don't intend to do that, you get the picture, sorry no DDLs thistime, and many tables have many nullable columns, joins slows down thesystem quite a bit, what's my best bet to improve its performance?change most of these nullable columns into non-nullable with defaultvalue of something like ' ', any thoughts along this line would beappreciated.
View 6 Replies View RelatedI have several tables that have the POLICY_NUMBER and POLICY_DATE_TIME in them.. All the tables with these two columns should have a POLICY_ NUMBER and a corresponding POLICY_DATE_TIME.I would like to find all tables that have POLICY_NUMBER = 123456 but do not have the corresponding POLICY_DATE_TIME..
View 1 Replies View RelatedHi,
I want to define Primary Key non-clustered on Nullable columns with datatype
as varchar ( 50 ) .
When i try to assign primary key , it gives me an error that
' Cannot define Primary Key contraint on nullable columns in table 'Table 1 '.
Could not create contraint . See previous errors . '
Any ideas how to resolve this issue ?
Thanks,
Anita.
I would like to dynamically copy a table in Transact SQL, like this:
SELECT * into NEW_TABLE from MY_TABLE where 1 = 2
This creates an empty NEW_TABLE as desired. However, NEW_TABLE retains the column nullability of MY_TABLE; I would like NEW_TABLE to have all its columns nullable.
I tried to write Transact SQL logic to update the isnullable column in syscolumns for NEW_TABLE, but was told that the entire SQL Server would have to be reconfigured to allow this.
Does anyone know of another way to do this?
I need to programatically create a mdb file which will contain nullable columns. I am using C++ with ADOX for the table creation and ADO to perform the table update.
Although ADOX seems to create the table ok, Table->Columns->Appends does not set the fields as adColNullable as expected.
When I insert data using ADO::Recordset->AddNew the following error occurs :- "The field 'MyTable.Column 2' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field."
Am I on the right tracks here or do I need to adopt a different approach?
Code block to illustrate the problem :-
Code Block
ADOX::_CatalogPtr m_pCatalog;
ADOX::_TablePtr m_pTable;
ADOX::_ColumnPtr m_pCol1;
ADOX::_ColumnPtr m_pCol2;
ADODB::_ConnectionPtr m_pConn;
ADODB::_RecordsetPtr m_pRs;
//ADOX - Create Data Source
_bstr_t strcnn(("Provider='Microsoft.JET.OLEDB.4.0';Data source = C:\test.mdb"));
m_pCatalog.CreateInstance(__uuidof (ADOX::Catalog));
m_pCatalog->Create(strcnn);
m_pTable.CreateInstance(__uuidof(ADOX::Table));
m_pTable->PutName("MyTable");
m_pCol1.CreateInstance(__uuidof(ADOX::Column));
m_pCol1->Name = "Column 1";
m_pCol1->Type = ADOX::adVarWChar;
m_pCol1->DefinedSize = 24;
m_pCol1->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol1->Name, ADOX::adVarWChar, 24);
m_pCol2.CreateInstance(__uuidof(ADOX::Column));
m_pCol2->Name = "Column 2";
m_pCol2->Type = ADOX::adVarWChar;
m_pCol2->DefinedSize = 24;
m_pCol2->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol2->Name, ADOX::adVarWChar, 24);
m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable));
//ADO - Data Access
m_pConn.CreateInstance (__uuidof(ADODB::Connection));
m_pConn->Open(strcnn,_bstr_t(""),_bstr_t(""),ADODB::adOpenUnspecified);
m_pRs.CreateInstance(__uuidof(ADODB::Recordset));
m_pRs->Open("MyTable", _variant_t((IDispatch *)m_pConn,true),ADODB::adOpenKeyset, ADODB::adLockOptimistic, ADODB::adCmdTable);
// Define a SafeArray that contains field names.
SAFEARRAY * psaFields;
SAFEARRAYBOUND aDimFields[1];
aDimFields[0].lLbound = 0;
aDimFields[0].cElements = 1;
psaFields = SafeArrayCreate(VT_VARIANT, 1, aDimFields);
// Create a SafeArray for values.
SAFEARRAY * psaValues;
SAFEARRAYBOUND aDimValues[1];
aDimValues[0].lLbound = 0;
aDimValues[0].cElements = 1;
psaValues = SafeArrayCreate(VT_VARIANT, 1, aDimValues);
long ix[1];
_variant_t var;
//Insert Data
ix[0] = 0;
var = "Column 1";
SafeArrayPutElement(psaFields, ix, (void*) (VARIANT *) (&var));
ix[0] = 0;
var = "test data row 1 col 1 Only";
SafeArrayPutElement(psaValues, ix, (void*)(VARIANT *) &var);
_variant_t vtFields, vtValues;
vtFields.vt = VT_ARRAY | VT_VARIANT;
vtValues.vt = VT_ARRAY | VT_VARIANT;
vtFields.parray = psaFields;
vtValues.parray = psaValues;
m_pRs->AddNew(vtFields, vtValues); //!! Fails Here !!
m_pRs->Update();
m_pRs->Close();
m_pConn->Close();
Question.I have a new table that I am adding to a script that I wrote. Thistable has 3 fields, the first 2 fields are used in the on statement asbeing = other fields in the script.The first field always has data in it, but the 2nd field is sometimesnull.So my problem is if both fields have data in them and they both matchto the data in the fields that I am linking them to, then it returnsthe 3rd field without a problem. However if the 2nd field is null thenit is returning a null for the 3rd field. I have checked and the fieldthat I am linking to is null also.So if I haveselect t1.field1, t1.field2, t2.field1, t2.field2, t2.field3from table1 t1join table2 t2on t1.field1=t2.field1 and t1.field2=t2.field2with 2 records in each tabletable1: record1: data, datarecord2: data, nulltable2: record1: data,data,datarecord2: data,null,datawhat I get from the script isrecord1: data, data,data,data,datarecord2: data,null,data,null,nullI would expectrecord2: data,null,data,null,dataI hope this makes sense, I didn't want to post the entire actual scriptas it is about 150 lines long.Thanks in advance.
View 5 Replies View Relatedhow I would work out the max value of 3 max date values. Note the max date values may be null.
Here is my code :
SELECT TD.sro_num, SRO.description, SRO.cust_num, custad.name, SRO.sro_type, TD.whse, TD.CostCode, TD.stat_code, TD.[No of days in Status],
SRO.total_cost_lbr, SRO.total_cost_matl, SRO.total_cost_misc, ((SRO.total_cost_lbr) + (SRO.total_cost_matl) + (SRO.total_cost_misc)) AS total,
convert(varchar,MAX(mt.trans_date), 103)as max_matl_transdate, convert(varchar,MAX(lbr.trans_date), 103)as max_lbr_transdate, convert(varchar,MAX(misc.trans_date), 103) as max_misc_transdate
[code]...
How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4, Now my query look likes this:
Select col1, col2,col3,col4,
(col1 + col2) as addcol1,
(col2 + col3) as addcol2,
(col3 + col4) as addcol3,
Max(addcol1,addcol2,addcol3) as maxvalue
from Table A
I am getting error as max accepts one argument, I cannot use case statement as table is already bulky and it will make my query more expensive.
I have two columns in my table. Both the columns contains datetime datatypes. I need to write a stored procedure which will calculate the date difference between the two columns and if it exceeds more than 5 days then that record should get deleted. How to do it?Thanx
View 2 Replies View RelatedHi Friends,
me again. I am trying to find out if all of my intended foreign keys are actually set as foreign keys programmatically.
Ive stuffed something up, please find my error
USE RIQDB1
SELECT DISTINCT 'Alter table '+ table_name +
' ADD CONSTRAINT DF_'+ table_name + '_' + column_name +
' DEFAULT ' + ''''' FOR '+ column_name FROM Information_schema.columns
WHERE ((column_name Like '%fk%') AND (FROM Information_schema.type = F))
go
thanks
Cm
I have many tables in one database, and some of them are empty. How can I list / find (for eg. names) only non-empty tables?
View 2 Replies View RelatedWe have about 60 several tables with order id as a column in all of them . Every day I have to check if certain order id’s exist in the 60 tables. I just need to get the name of the table if it exists. Is there a way to dynamically keep changing the name of the table instead of having all 45 tables in the script?
View 2 Replies View RelatedI have a front end inteface that adds data to tables in a SQL database.My question is:Is there an easy way of finding out what tables are affected by the update? Is there a sp_ or DBCC command that would give me the update or altered tables?
View 2 Replies View RelatedHi All,
How to find all the tables of a database containing a column . for example,how list all the tables of employee database having employeeid ?
Cheers,
Mathi
India.
I am attempting to document a sql server 2000 based accounting system.Is there any way to see what tables a stored procedure affectswithout diving into the code?Regards,Ty
View 1 Replies View RelatedBeing fairly new to SQL and SQL scripting, I am at a loss on how to proceed on my issue.
I have a MSDE database with 2 tables that need to modified. I am changing to a standard 12 digit code in my PATIENTS table for the field sChartCode nvarchar). That code will be in the form of 110012345678. 1100 will preceed the actual 8 digit chartcode
In the PATIENTS table, the same person may be duplicated many times using vaiations such as 123456, 12345678, 012345678, 12345678 SMITH, 012345678 SMITH. For each of these records, they are linked to the RECORDS db using the field lPatientId (int).
I have already manually updated about 20K records in the RECORDS db which
takes way to many hours of time. New records will be imported at about 10K a week or so and will be over 100K soon. By the way, the SQL server is on the way.
What I am looking for is an easier way to find the records that have not been
converted in the PATIENTS db and see if they match one that has already been converted. If it has, it would need to update all records in the RECORDS db with the correct updated lPatientId and then delete the duplicate record(s) from the PATENTS db. If not, it would only need to add '1100...' to the lPatientId field.
Any help or guidance that anybody can give will be most appreciated.
Dale
Hi,
I have a legacy sql 2000 database with numerous stored procedure and tables.
I need to find out the top 20 most used/accessed tables in the database
Any one know how i could do this type of trace with profiler?
Here's the situation, developers inherit a web app from someone,backend SQL db has about 120 user tables and the db is also being usedby other apps. Developers don't have a list of user tables being usedby this app, now, I need to create a new db based on this one, whichwould be used by this app only. So, I intend to find all the usertables being used by this app, then copy its schema and possibly dataas well to a new db. FYI, current ERD is not this app.One option to find out all the user tables being used by this app is todo recursive search for FROM and JOIN against the full spectrum of theuncompiled source code, then, weed through such an extracted list whenin doubt about a particular table, one caveat is a portion of code withsql stmt could have already been commented (no longer being used thoughstill in the code).Better option?TIA.
View 3 Replies View Related
Hello,
I have two tables with the same field layout, and they both have the same field as the Primary Key. They just contain different data. I would like to know if a record exists in one, or both, tables.
The tables are InvTemp1 and SalesTemp1. The key for both is stock_number.
Here is the command so far:
SELECT COUNT(*)
FROM InvTemp1 INNER JOIN SalesTemp1 ON InvTemp1.Stock_number = SalesTemp1.Stock_number
WHERE (InvTemp1.Stock_number = '101053')
Thank you for any ideas,
Tom
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
View 4 Replies View RelatedI am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;
In our Production db we have all most all tables have the column created or createdate.
I need to find out all tables without the created or createdate column
SELECT t8.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c
ON t8.OBJECT_ID = c.OBJECT_ID
[code]....
Below I have a query which list the relations (constraints) between tables.
I want to list all the relations which are visible in the Database Diagrams.
The list is not complete. How do I get a complete list ?
--
-- Query to show the relations (constraints) between tables.
-- This does not show the complete list
--
SELECT A.constraint_name,
B.table_name AS Child,
C.table_name AS Parent,
[Code] ...
Hope this is in the right thread, sorry if not!
I have run into a problem, i need to find out that column(s) in a table that makes the primary key.
I thought that this code did the trick.
***
DECLARE @c varchar(4000), @t varchar(128)
SET @c = ''
SET @t='contact_pmc_contact_relations'
Select @c = @c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @t and k.colid = c.colid ORDER BY c.colid
SELECT Substring(@c, 1, Datalength(@c) - 1)
***
This works in most of my cases. But i have encounterd tabels where this code doesn't work.
Here is a dump from one of the tabels where it doesn't work.
SELECT *
FROM sysindexkeys
WHERE (id = 933578364) <--id of the table
***
id indid colid keyno
933578364 1 1 1
933578364 1 2 2
933578364 2 1 1
933578364 3 2 1
933578364 4 3 1
933578364 5 4 1
933578364 6 5 1
933578364 7 6 1
933578364 8 7 1
Not sure if that dump made any sense, but i hope it did.
If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).
So, anyone know how i could solve this problem?
Regards
/Anders
I have two table People and Employee, both have firstname and lastname as fields
I want to display only the names that don't match on firstname and lastname
I have a CRM database that has a lot of tables and would like to be able to extract the last 'x' records in descending order from each table based on a common a field 'modifiedon' that is in every table and is auto populated by the system.
View 4 Replies View RelatedCREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
View 6 Replies View Relatedgetting result as given below.
Input:
id Name Data
101 AA PQ102BBRAJAKIRANBUBLU
102 BB RS101AAEROJUCHALABAGUNDI
103 CC TU104GGANDICANKILLANYONE
OUTPUT:
id Name Data
101 AA 101AAEROJUCHALABAGUNDI
102 BB PQ102BBRAJAKIRANBUBLU
103 CC
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
I have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference?
Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.
Is there a way to set a column nullable in a script if all I have is the column name, something like:
exec('ALTER TABLE xyz ALTER COLUMN ' + @col_name + ' NULL')
The problem is that ALTER TABLE requires the type, so you have to say something like:
exec('ALTER TABLE xyz ALTER COLUMN ' + @col_name + ' decimal(12,4) NULL')
But the data type is not given to the script, so it is stuck.
I realize that syscolumns provides some relevant information, but it is not clear how to convert its information to a string like "decimal(12,4)". It seems, for example, that a decimal type has syscolumns.xtype = 106; however I can find no documentation on this, nor am I assured I can get all the right codes by trial and error. Does anyone know of a clear means of getting this information?
What happens if a column which has been defined so that it should not be Nullable is passed a value which is null?
i.e. IDNumber - Primary Key and Nullable = No.
Thanks