I thought I saw this done once before. So today I hunted around in
Books OnLine and did a Google search. So far I have found nothing
close. So if you know how to do it, please tell me or if cannot be
done, I'd appreciate know that too.
Thanks in advance,
IanO
When I run the parse command in SSMS, it merely does a syntax check. When I run through the export data wizard by right clicking on a table, it allows a query as the data source. When I click on the parse button in the window that accepts that query, it resolves object names and notifies me of invalid ones. I'd really like the same thing to happen when I parse in SSMS...
View 5 Replies View RelatedHi,I've run into a curious problem with MS SQL Server 8.0. Using sp_help andSQL Query Analyzer's object browser to view the columns returned by a view,I find that sp_help is reporting stale information.In a recent schema change, for example, someone lengthened a varchar columnfrom 15 to 50 characters. If we use sp_help to find out about a view thatdepends upon this column, it still shows up as VARCHAR(15), whereas theobject browser correctly reports it as VARCHAR(50).Dropping and recreating the view fixes the problem, but we have quite a fewviews, and dropping and re-creating all of them any time a schema change ismade is something we want to avoid. I tried using DBCC CHECKDB in hopes thatit would 'refresh' SQL Server's information, but no luck.(if you're curious as to why I don't just use the object browser instead,read boring technical details below)Has anyone seen this before? Is there some other way (other thanre-creating every view) to tell SQL Server to "refresh" it's information?Thanks!-Scott----------------------Boring Technical Information:The reason this is an issue for us (i.e., I can't just use the objectbrowser instead) is that our object model classes are built using standardmetadata query methods in Java that seem to be returning the same staleinformation that sp_help is returning. These methods are a part of thestandard JDK, so we can't easily fiddle with them. Anyway, as a result, ourobject model (at least with respect to views) may not match our currentschema!
View 5 Replies View RelatedI just found an odd bug and was wondering if anyone else has seen this.Any templpate file in the directory 'C:Program FilesMicrosoft SQLServer80ToolsTemplatesSQL Query Analyzer' that has the NTFScompression turned on (that is, colored blue in Windows Explorer) doesnot display in the object browser's template tab of query analyzer.Control-Shift-Insert works fine to insert a template, however.What's up with that?
View 2 Replies View RelatedI have several default constraints defined on a table. When I use theObject Browser and expand the constraints for this table andright-click and then select "Script Object to New Window As Create", acreate constraint statement for a different default constraint isdisplayed than the one I just right-clicked on. For example, I clickon constraint "DF_C" and it shows me "DF_B".The last time I encountered this, the solution was to dump contents ofthe table into another, drop, recreate it, and restore the contents.That's not a good option this time.Is there another way to fised this or at least navigate the catalog tofind out what is "off" about this?Thanks
View 1 Replies View RelatedI want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql
App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB
OUTPUT SHOULD BE LOOK LIKE BELOW
APpName OS Adder Flag
App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null
App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE
In SQL 2005 - when I display the results of a View and Copy all rows and columns, the resulting Paste in Excel does not include the column names.
How can I set SQL 2005 so the names of the columns will come along with the content of the copy function?
Background:
When I am using a SQL Query (instead of a view) I have the ability to control whether or not I am able to Include the Column Headers when copying or saving results. The control exists in the Options > Query Results > Results to Grid > Include column headings etc.
My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query.
Thank you,
Poppa Mike
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.
When I am using a SQL Query I have an ability to control whether or not I am able to Include the Column Headers when copying or saving results.
The control exists in the Options > Query Results > Results to Grid > Include column headings etc.
My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query.
The idea is that when I setup a view it€™s a drag/drop type of query building (query building for dummies if you will). Once I have a view and click the Execute icon it will return all the records selected by the View. However, when I click the upper left/top box to select all rows and column and then try to copy/paste the records into Excel all the data copies just fine but the field name/column headers are not there.
How can I get the header fieldname date to copy/paste from View result set that I'm able to copy from a Query result set?
Thank you,
Mike
Hi all,
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing:
dbo.Categories
dbo.CustomerCustomerDemo
dbo.CustomerDemographics
dbo.Customers
dbo.Employees
dbo.EmployeeTerritories
dbo.Order Details
dbo.Orders
dbo.Products
dbo.Regions
dbo.Shippers
dbo.Suppliers
dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing:
dbo.Categories
New Table...
Modify
Open Table
Script Table as |> CREATYE To |>
DROP To |>
SELECT To |>
INSERT To |> New Query Editor Window
File....
Clipboard
UPDATE To |>
DELETE to |>
From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance,
Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
Most of the times I cant see my database tables from the query analyser in the object browser window... There are some times that appear normally...but in mosto of cases they just dont appear at all...I can only see the master database and msdb... I am connecting giving the proper password...
bear in mind that I can write and execute queries to my database although I cant see the tables
Why could this be happened??? Is a matter that i have discussed with my hosting provider or I am missing something here???
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!!!
I want a query that returns only the column names
View 4 Replies View RelatedHi all,
By using below query i can get no of tables having the give column name in a particular database,
SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno')
but i want to know the table names too?
any one please suggest me how to find table names too......
In other words i know the particular column name and right now i want to know the table names in which this column name exists.
Hi All,
I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar.
Thanks a bunch.
Val
All, is there a way of getting all table names that contain a columnname?I'm looking at a DB that has 125+ tables and I'm interested in findingall table names that contain the column order_date.How can I do it?TIA,Sashi
View 2 Replies View RelatedIf a user is not authorized to see a database can I exclude that database from even appearing in Management Studio for that user
TIA,
Barkingdog
I have two different tables... one for all Staff, and another for all Temp Staff. I need both to output to a datagrid, and so I need to grab both tables from a SQL query to output to my datagrid, but I can't seem to get the logic right for it to work. Can someone give me some suggestions on why my results are blank when I'm running this query? I thought a simple join would allow both sets of identical column names to coexist in peace...SELECT TOP 100 PERCENT dbo.StaffDirectory.UserName, dbo.StaffDirectory.LastName, dbo.StaffDirectory.FirstName, dbo.StaffDirectory.Dept, dbo.StaffDirectory.Title, dbo.StaffDirectory.EMail, dbo.StaffDirectory.LocationFROM dbo.StaffDirectory INNER JOIN dbo.TempStaff ON dbo.StaffDirectory.Location = dbo.TempStaff.Location AND dbo.StaffDirectory.EMail = dbo.TempStaff.Email AND dbo.StaffDirectory.Title = dbo.TempStaff.Title AND dbo.StaffDirectory.Dept = dbo.TempStaff.Dept AND dbo.StaffDirectory.FirstName = dbo.TempStaff.FName AND dbo.StaffDirectory.LastName = dbo.TempStaff.LName AND dbo.StaffDirectory.UserName = dbo.TempStaff.UName AND dbo.StaffDirectory.MDNo = dbo.TempStaff.MDNoIs something wrong here? It just doesn't work =(Any suggestions would be really appreciated.Thank you
View 5 Replies View RelatedI wasn't sure whether or not to post this in the .NET or MsSQL forum, mods please move accordingly.
Say I have two tables: Store, and Area that have the following columns:
Area:
AreaID, AreaName, Description
Store:
StoreID, AreaID, StoreName, Description
When I run the following query :
SELECT * FROM Area, Store WHERE Area.AreaID = Store.AreaID, the following columns come back from the .NET SQLDataReader
AreaID, AreaName, Description, StoreID, AreaID, StoreName, Description
Even though the Description fields are different there is no differentiation between which one is which (other than an integer index)
Is there a way to form the SQL query so the columns come back
Area.AreaID, Area.AreaName, Area.Description etc...
I was hoping to do something like the following
Select Area.* as 'Area.'.* ...
but that wasn't liked by the SQL server, any ideas?
Note: The actual tables are more complex than this, and the table names aren't static. Individually aliasing columns won't work, nor will checking the alias.
-MBirchmeier
I have a table with almost 100 columns. Ex:
ID ColA ColB ColC
2 Null Word Excel
3 Access Paint Null
Expected result set is :
ID ColA ColB ColC ColumnNames IsEmpty
2 Null Word
Excel ColA
Yes
2 Null Word
Excel ColB
No
2 Null Word
Excel ColC
No
3 Access Paint Null ColA
No
3 Access Paint Null ColB
No
3 Access Paint Null ColC
Yes
I would like to list all the column names against each record of the table and and would like to keep track whether the column is empty or nonempty. How can I write SQL query for this?
I am using the following to extract the column names of a table. I would like to do this for the whole database. Currently I am cutting the results into an excel spread. Is there a better way of doing this? Here is the query
SELECT name
FROM syscolumns
WHERE [id] = OBJECT_ID('tablename')
I am trying to find the Table and column names from the below.
Is there a way i can get table name and column name from query_plan column?
SELECT TOP 100 Â text, query_plan,cp.plan_handle
FROM sys.dm_exec_cached_plans cpÂ
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
WHERE objtype='Adhoc'
 SELECT TOP 100  text, query_plan, cp.plan_handle, qs.last_execution_time
FROM sys.dm_exec_cached_plans cpÂ
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
 JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
WHERE objtype='Adhoc'
I have below output:
Text    Query_plan   Plan_handle   Lst_execution_time
Select id,name from person         Â
<Showplan... Â dshkkgdaHqrqe13232423 Â Â 2015-07-21 10:50:22.713
Update customer set Cid=3 where name='abc' Â Â Â
<Showplan... Â Â poasfvrqe13232423 Â Â 2015-07-21 10:16:22.500
delete from orders where  ORid=8  Â
<Showplan... Â Â 2ase2423 Â Â 2015-07-21 10:10:22.710
Select 1,2,3,4,5 from num Â
<showplan       afqfqfq   2015-07-21 10:10:22.710
Â
I am looking forÂ
Text      Query_plan          Plan_handle    Last_execution_time     TabName colname
Select id,name from person  <Showplan... dshkkgdaHqrqe13232423   2015-07-21 10:50:22.713 Â
Person Id, name Update customer set Cid=3 where name='abc' Â
<Showplan  poasfvrqe13232423  2015-07-21 10:16:22.500  customer  Cid,  name
delete from orders where  ORid=8  <Showplan...  2ase2423    2015-07-21 06:10:22.710   Orders   ORid
Select 1,2,3,4,5 from num <showplan       afqfqfq   2015-07-21 10:10:22.710     nUM    1,2,3,4,5
Hi all,
Is there a way i can write a script that will sort the column names of my table?
Example:
Table 1 columns:
First_Name
Last_Name
Address
City
Country
I want to write a script that will sort them like this:
Address
City
Country
First_Name
Last_Name
I know it is basics, just slipped out of my mind, How do we list or print the columns names of table in sql server 2000.
thanks,
I've installed SQL Server 2005 SP1 RTM on a Windows 2003 R2 machine (machine A), and my local workstation (machine B).
From machine B, I can use SSMS to connect to SQL Server on machine A. This is using either "New Query" or "Object Explorer".
However, on machine A, SSMS can only connect to machines A or B using the "New Query" option. If I try "Object Explorer" for either, I get the error message "Failed to retrieve data for this request... urn could not be resolved at level Configuration."
I'm not sure what died (no pun intended) since the last time this worked. Any thoughts on this?
/*Code below raises following errors:Server: Msg 245, Level 16, State 1, Line 6Syntax error converting the varchar value 'a' to a column of data typeint.*/create table #x (i integer, c char(1))create table #y (c char(1), i integer)insert into #x VALUES (1, 'a')insert into #y SELECT * from #xdrop table #xdrop table #y--CODE COMPLETEIs there a way to use the column names as the basis for the insert asopposed to column position? The key here is that I DO NOT want toSPECIFY COLUMN NAMES (i.e. 'insert into #y (i,c) SELECT * from #x')Regards,Ty
View 3 Replies View RelatedCan anybody tell me how to find out whether a particular column exists in any of the tables of a database and if it does, display the table names?
Thanks
Hi all,
I'm new to this whole trigger 'thing', so forgive me if this question has been asked and answered a few times already.
I'm in the process of writing a trigger that will send an e-mail to an application admin when any table within a given database is altered (IE, a column is added or deleted). I can get the e-mail to fire off when that happens without any issue, but I'd like to be able to let the admin know which table was tweaked and what new column was added.
Is this a relatively easy thing to do and I'm just not finding the right built-in variable name, or does something more need to be done?
Thanks,
-Mike
How do I find what is keeing SSMS Object explorer from giving a list of tables or stored proc's? Even when you filter on what you are looking for? This will clear up in time, so I am guessing that some process is placing a lock .. but when I run the blocking query, below, I get no result.
SELECT DTL.[resource_type] AS [resource type]
, CASE WHEN DTL.[resource_type] IN ('DATABASE','FILE','METADATA')
THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
[Code] .....
I have a column of digits I'd like to copy into another column in thesame table. How would I do this?Thanks,Bill
View 1 Replies View RelatedI would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours
@DayName varchar,@DayIDName varchar
AS
BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName
INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName
INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID
END
RETURN
...is this possible?? if so how?
Thanks
Hi,
I have a SP which returns a select query on a temp table so I get to choose column names
when I create the #table.
Can I determine column names myself based on the results of another query (in the SP)
before, or (preferably) after I create the #table and populate it.
My query is used to bind to a datagrid so I could use....
dataGrid.Columns[index].HeaderText and set it to a particular output parameter, but I want
to keep the code in the SQL.
Cheers