Newbee Help Needed, I Need To Find Column Names If Any After 2 “check” Columns.
Sep 15, 2002
I need to find column names if any after 2 “check” columns.
Scenario: I have a database, with approx 400-1500 tables, depending on installation of software. The software is structured so that, when it synchronizes the SQL database it will create all the columns e.g. custacc, custname etc. and then it will always put in two check columns “CheckOne” and “CheckTwo” these two columns has to be the two last ones. In 99.9 this always works fine, but sometime if the users creates a new field in the software, when it synchronizes the new field “lands” behind the two checkfields, which is not good.
So what I am after is a script, which can run through all user tables, tell me if there are columns after the two checkfields and list those tables if any.
Any help would be greatly appreciated.
Cheers
Henrik.
View 3 Replies
ADVERTISEMENT
Sep 11, 2015
how to find the columns names in a table in sql 2008r2.
as i need to compare the midsing fields in the table from two database.
View 3 Replies
View Related
May 29, 2015
I have a SQL text column from SP_who2 in table #SqlStatement:
like 1row shown  below :
 "update Panel  set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"
I want to find what table and column  names are in the text ..
I tried like below .. Â
Select B.Statement from #sp_who2 A Â
LEFT JOIN #SqlStatement B ON A.spid = B.spidÂ
 where B.Statement IN (
SELECT T.name, C.name FROM sys.tables T
JOIN sys.columns CÂ
ON T.object_id=C.object_id
WHERE T.type='U'
)Â
Something like this : find the column names and tables name
View 18 Replies
View Related
Jan 11, 2007
hi,
i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.
i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.
i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.
thanks in advance
View 2 Replies
View Related
Jan 7, 2015
I am trying to figure out how to pivot a temporary table. I have a table which starts with a date but the number of columns and columns names will vary but will be type INT (Data, col2,col3,col4………….n)
So it could look like
Date , TS-Sales, Budget , Toms sales
01-Jan-14,100,120,300
02-Jan-14,80,150,300
03-Jan-14,100,20,180
Turned to this
01-jan-14, 02-jan-14, 03-jan-14
100,80,100
120,150,20
300,300,180
Or even just the date and a SUM
What I want is to be able to sum al the columns but without knowing the name and the amount columns to start with this is a manually processes. How could I automate this?
View 2 Replies
View Related
Feb 26, 2015
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)
[Code] ....
These are imported from an Excel Workbook so that's why all the columns with spaces for now.
View 9 Replies
View Related
Oct 19, 2015
how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.
Example Data
create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),
[code]....
View 4 Replies
View Related
Nov 16, 2015
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
 1, View1, Column4
 2, View2, Column4
 2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
View 3 Replies
View Related
Jun 12, 2008
I have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
View 5 Replies
View Related
Jan 22, 2004
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
View 5 Replies
View Related
Aug 1, 2014
As part of my backup routine, I have a SQL job for each DB which which is called "DB-NAME - Backup Job"
I need to put a script together to check that each database has a backup job associated to it.
select * From sys.sysdatabases where name not in ('msdb','model', 'master', 'Tempdb', 'DBA') order by name desc
select * from msdb.dbo.sysjobs order by name desc
I know all the details i need are in there, but i cant figure out the best way to tackle it. Do I need to do a cursor to go through each DB name, put it into a variable, then select the job name where name like '$variable%' ?
View 7 Replies
View Related
Feb 26, 2008
Hello experts!
I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime)
>= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR
datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
Any help is appreciated.
View 3 Replies
View Related
Jul 3, 2015
When i have multiple sql server instances running on one server, how to check the names of that instances with out connecting to sql server management studio.
View 12 Replies
View Related
Sep 8, 2007
To find parameter names
In ASP.Net 2.0, how can I get what parameters defined in an rdl report file? I know the report name and the report is deployed to a server.
View 1 Replies
View Related
Dec 13, 2001
Hi all, I need a favour from you... can i do a list to find out the table names used by a stored procedure. remember it's around 700 Stored Procedures. and following a rule <databasename>.dbo.<tablename>
View 1 Replies
View Related
Feb 14, 2007
Thank you for taking the time to read this, I need all the advise and help I can get on this ... so please post anything you think would work ... A little confused I am:
Have a database table called "people" with "person name" and "ID" field. My ASP.NET application mainly stores articles in article table. An article's Article text mentions various people's names in different combinations (e.g. John, Smith, John Smith, Smith John, etc)
Is there any way, I could compare the article text stored in article table with people table and get the people from people table along with their ID's who have been mentioned in that article? ... so in an article "i love john smith ... and i think Mr smith has always been helpful", I get John Smith back...
Not too sure being honest, what is the best way of implementing this, looking for the most efficient way, probably using XML? SQL Query or may be ASP.NET's code behind?
Thanks once again for taking the time.
Cheers,
Tyro
View 4 Replies
View Related
Mar 10, 2001
I have a database and I want to retrieve informations about all tables
defined in this database. In other words I need the table list defined in
database. Can you help me?
Thank you.
View 1 Replies
View Related
Oct 20, 2015
Is there a way to find all the table names that have a foreign key that references FILE_ID_IN([FILE_ID])
Note: The table def below has a foreign key
IF object_id('CODE_MAP_IN', 'U') IS NOT NULL DROP TABLE CODE_MAP_IN
Print 'Creating table CODE_MAP_IN...'
Create table CODE_MAP_IN
(
[CODE_MAP_FR] [varchar] (53),
[CODE_MAP_TO] [varchar] (53),
[FILE_ID] [float] DEFAULT 1000
PRIMARY KEY ([CODE_MAP_FR]),
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])
) ON [PRIMARY]
GO
View 2 Replies
View Related
Jul 20, 2005
Hi Alljust been given a project converting clipper app with Dbase filesinto Windows using "CA Visual Objects"Now my question: My objective is to keep the selling price of the appas low as possible but I need SQL database.MS SQL 2000 is 1st price I know but what els is available at minimalcost. ?Is MySql an option to look at ? and what pitfalls are there?Gunter
View 2 Replies
View Related
Mar 19, 2004
I need a querry to get all columns names.
thanks
View 4 Replies
View Related
Apr 9, 2008
Hi,
I need a query to get the index names of particular tables. for eg.. i have some tables like emp_data,emp_job....etc..Now i want to find all indexe names for those tablenames that starts with emp........ Plz help me...
View 6 Replies
View Related
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
Feb 12, 2003
Hi.
I`m new to MS Sql and have been studying some tutorials and stuff.
What I can`t get a hold of is this sysname-thing. I see it is referenced to when i.e you create a new table...
Can anyone help me?
Freddy....
View 1 Replies
View Related
Oct 1, 2006
Hi guys,
I am a newbee withh SSIS, I used DTS in SQL 2000 way back to migrate data from AS/400 DB2UDB iSeries to SQL 2000. It was easy to use DTS just specify Source and Destination and do some mapping and bingo. But now how do I do the same things with SSIS?
Is there some sort of tutorial out there? Any nice books? Any help is greatly appriciated.
T.I.A.
View 4 Replies
View Related
May 28, 2004
Hey,
I'm creating registration form.
To show fields names I thought to read columns names.
It's ok if columns is named like "Name", "Age" etc.
But if the columns is named [Country, Address, PostCode] then, I think, it can course some problems. Am I right?
First problem I thought about - changing database in the future (Now MS SQL 2k to MySQL etc.)
Is this the only problem?
To solve this I think using table which store syscolumn names as user defined columns names.
My system is speed critical and using this I would get less performance.
Which way should I go?
Case saving columns names in table, how to generate safe column name from user specified name, which can have special charters.
Thanks
View 6 Replies
View Related
Oct 16, 2004
Hi, I need do get a columns names from a table? How to do this in pure SQL? I thought about creating a stored procedure or user function with a result of a string ( col1name,col2name ....) I do not know how to count the number of columns in a specyfied table? Any help would be appreciated. Thanks in advance. magicxxxx
View 2 Replies
View Related
Apr 28, 2008
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
View 1 Replies
View Related
Nov 10, 2013
1. I need to find the names of the customers who have purchased academic books. (Coding required as Subquery NOT as Join)
2. Here, I need to show a list of authors who have written books and list the books they have written.
Sort the list by last name
(Coding required as Subquery)
View 4 Replies
View Related
Aug 31, 2004
Hi,
I am looking for some help in MS SQL server. I want to access the columns of a table without using the names of the colulmns.
Example - SELECT table1.field[1], table1.field[2] FROM table1;
Any information to this effect is much appreciated.
cheers/- Pradeep
View 9 Replies
View Related
May 8, 2007
I have one column name that is: description
when i write a query the world lights up with blue, I think I saw someone using [ ] around the word but I no longer remember if this is the way to handle reserve words that have been use as columns names
View 10 Replies
View Related
Jun 27, 2002
Hi. I have a database online. I have to run some scripts, and check out my database. The ISP i have, has currently changed the IP address for the database, now I can not hook up with it via Enterprise Manager. Can someone help me. I am using Microsoft ME as the platform.
View 1 Replies
View Related
Mar 12, 2008
hi all,
i have some questions about backing up mssql database, please, if you can help me with them.
1. what is main difference between Full Recovery and Bulk_Logged Recovery models? when to use one, and when to use the other?
2. why do i need marks in my backup? can you tell me in which case do i need marks? i don't understand, why wolud i ever wanted to recover database to some marked point? in which case i would like to recover only it's part and not full database?
3. if i do have backup of transaction log, it means nothing if i do not have backup of database structure? i need to have complete backup of transaction log to be able to recover all of the data?
4. i have database, and i did make no changes in database structure. in that case, only one backup of the system tables of the database is enough? there is no need to backup it all the time?
5. when i backup my transaction log file, do i backup only a differences between last backup of transaction log file and current, or i always backup complete transaction log file?
thank you in advance!
View 3 Replies
View Related
Mar 31, 2006
I've called a resultset from SQL Server
using an SQL Selection. I need to iterate over that entire result set
(200+ columns/fields) and all I need are the random numbers contained
in any of the rows/columns. I don't want to have to name each
field/column and then use an if > 0 statement.Isn't there
some way to generically loop through the column's by index or something
instead of their field name so I can just use an integer loop to walk
the dataset? I know there is I've done it about 5 years ago. The
question is how do you do it in C#?SqlConnection thisConn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLQuery"].ConnectionString); SqlCommand thisCmd = new SqlCommand("Command String", thisConn); thisCmd.CommandText = "Select * from SelectionsByCountry where [" + DropDownList1.SelectedItem.ToString() + "] > '0'"; thisConn.Open(); SqlDataReader thisReader = null; thisReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection); while (thisReader.Read()) { DropDownList2.Items.Add(thisReader["System"].ToString().Trim());/*** There are 200+ columns left I want to walk over using a loop structure of some sort. How do I do that?*/ }- Rex
View 2 Replies
View Related
