Transact SQL :: Bulk Column Names Rename
Aug 4, 2015Is there a way to bulk remove spaces from column names from all tables in a db?
View 6 Replies
ADVERTISEMENT
Transact SQL :: Bulk Renaming Table Names
Aug 14, 2015How can I bulk remove spaces within table names in the whole db?
View 3 Replies View RelatedTransact SQL :: Alter Table To Rename Column Name With No Space
Oct 27, 2015I want to alter my all tables to change the name of all columns.
I need this because all column names were created with space and I want to remove the space for future work .
For example – In Table Customer there is a column name [Cust_Id ] and I want to change it with [Cust_Id]
Transact SQL :: Get Table And Column Names From XML
Jul 6, 2015I 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'
Transact SQL :: Getting Table And Column Names
Jul 21, 2015 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
Transact SQL :: Vertical Column Names With Data?
Jul 14, 2015I have a table as below and need getting the desired result as below
Col1 Col2 Col3
A B
C
---desired result
t1 t2
Col1 A
Col2 B
Col3 C
[URL]
Transact SQL :: Additional Column With Bulk Insert?
Aug 11, 2015Need to know a mode whereby somehow I can every time insert an additional column in a table while bulk inserting data to an existing table from a new flat file thus identifying the file from which, or the time when, the data was inserted in an existing table.
View 2 Replies View RelatedTransact SQL :: Find Table And Column Names From String Data
May 29, 2015I 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
Transact SQL :: Renaming Pivot Table Column Names Dynamically
Oct 30, 2015I got a table which gets populated by stor proc where we pivot the Sum(Balance of mortgage) by YYYYMM for the whole duration of the loan term.
I have a requirement to rename the column header where the previous month end balance period be renamed to P0.
if we run the report today, then the balance as at 31/09 should show under column P0 which now shows under 201509 and then P0 keeps shifting with each month run.
How do I dynamically rename the column headers.
Transact SQL :: Creating Dummy Headers (column Names) With Union All
May 19, 2015I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....
COL 1 COL2 COL3 COL4 etc. BEH BIG BPL etc.
************************************* INTAKT DEFEKT INTAKT DEFEKT INTAKT
************************************* B E B E B
I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?
Transact SQL :: Using Bulk Insert - Invalid Column Number In Format File Error
Jun 5, 2015I try to import data with bulk insert. Here is my table:
CREATE TABLE [data].[example](
col1 [varchar](10) NOT NULL,
col2 [datetime] NOT NULL,
col3 [date] NOT NULL,
col4 [varchar](6) NOT NULL,
col5 [varchar](3) NOT NULL,
[Code] ....
My format file:
10.0
7
1 SQLCHAR 0 10 "@|@" 2 Col2 ""
1 SQLCHAR 0 10 "@|@" 3 Col3 ""
2 SQLCHAR 0 6 "@|@" 4 Col4 Latin1_General_CI_AS
[Code] .....
The first column should store double (in col2 and col3) in my table
My file:
Col1,Col2,Col3,Col4,Col5,Col6,Col7
2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS
2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS
My command:
bulk insert data.example
from 'R:epoolexample.csv'
WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)
Get error:
Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".
I changed some things as:
used ";" and "," as column delimiter
changed file type from UNIX to DOS and adjusted the format file with "
" for row delimiter
Removed this line from format file
1 SQLCHAR 0 10 "@|@" 2 Col2 ""
Nothing works ....
Rename Server Names In Enterprise Manager?
Apr 17, 2004Hi there.
Sorry 'bout my bad english.
Is there any possibility, to change the current server name from the stuctureview in the Enterprise Manger. I'm registering a lot of online-servers from my customers. The remote servername is fixed of course, but I want to change it local, to explain it a little bit better
(e.g. "W2KSQLSRV" -> "CustomerXY")
Can someone give me a hint, please?
Thanx.
Determine Table Names And Column Names At Runtime?
Jan 22, 2004Hi
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.
Transact SQL :: Trigger - Want To Get Email If Someone Rename Database
Nov 10, 2015Trying to find tsql for TRIGGER which will send me email, if someone rename database in test/dev environment. I found it for CREATE and DROP database trigger but could not find for RENAME database.
View 8 Replies View RelatedTransact SQL :: Rename Existing Table With Date Suffix
Apr 23, 2015I'm trying to rename a table with date suffix at the end of the table name, and drop the date suffix table which is greater than 7 days. for that I have the below sql, I have not included the drop syntax in this.
I'm not able to rename with the date suffix in the below sql, syntax error at '+'
DECLARE
@TPartitionDate date
IF EXISTS (SELECT * FROM sysobjects WHERE Name = 'IIS_4')
BEGIN
SELECT
@TPartitionDate = MAX(PartitionDate)
FROM PartitionLog (NOLOCK)
EXEC sp_rename 'IIS_4','IIS_4_'+ @TPartitionDate
END
How To Rename A Column?
Dec 1, 2004Hi,
Please let me know which query we will to rename a column in a table. I am using MS-SQL 2000.
Rename Column?
Jul 20, 2006Hi,
Is there a way to rename column like below?
ALTER TABLE table_name RENAME COLUMN from_column TO to_column
the statement above i found it from a web site, did not worked...
How To Rename Column Name
Feb 29, 2008Hai
XYZ (Table name)
a (column name)
Now I have to rename column name a to abc.
alter table xyz
RENAME column a to abc
With this statement, I'm not getting. Its throwing an error
Error : Incorrect syntax near the keyword 'column'.
Can anyone solve this
Thanks in Advance.
Suresh Kumar
Rename The Column By Using The Dynamic Value
Feb 5, 2008Hi All
I want to rename the column name by using the dynamic value as like
declare @name as varchar(12) set @name='ss'
sp_rename 'dbo.employees.id3',@name, 'COLUMN';
go
but it shows the error
Incorrect syntax near 'sp_rename'.
any idea?
Rename Primary Key Column?
Jul 22, 2014will there be any issues if i rename a primary key column name,which is already beging referenced as a foreign key by other tables.
View 1 Replies View RelatedRename Column At Runtime
Oct 20, 2007
I am have this problem, that would not go away, can some please help me.
I want to rename or delete column that is bound to a tableadapter using SQL Express at runtime. is it possibleI have no idea on what to do.
How To Rename A Column In SqlCE?
Apr 25, 2007When I execute EXEC sp_rename 'PATHSPECIMEN.PVVALUE', 'PVPROC', 'COLUMN', I am getting the following error:
Major Error 0x80004005, Minor Error 25621
> EXEC sp_rename 'PATHSPECIMEN.PVVALUE', 'PVPROC', 'COLUMN'
The specified argument value for the procedure is not valid. [ Argument # = 3,Name of procedure(if known) = sp_rename ]
Could some one guide me where I am wrong?
I am executing this query against SqlCE in Sql Server 2005 Management Studio.
Unconditionally Select Column And Rename
Oct 7, 2004I would like to select the second column from a table reguardless of what it is named and rename it AS Description. Is there a way to do that without knowing the name of that Column?
View 1 Replies View RelatedRename A Column And Its Data Type
Feb 27, 2008What is the way to rename a column in a table along with its datatype.
Thanks
How To Rename A Column Or Table On The Desktop?
Feb 28, 2007I am using the compact edition on a desktop using VS2005 as well as SQL Server Management Studio. None of those tools allow me to rename a column or rename a table. Can someone point me a tool the runs on the desktop (as opposed to running on a CE device) that allows me to do the renaming?
Thanks
Replication :: Rename A Column In Replicated Table
Jan 29, 2010I tried to run this(below) the table is replicated(transactional).
EXECUTE
sp_rename N'dbo.Tablename.Columnname, N'New_Columnname', 'COLUMN'But getting this error message:
Msg 15051, Level 11, State 1, Procedure sp_rename, Line 227
Cannot rename the table because it is published for replication.
How Do You Use An Identity Column When Doing A Bulk Insert Using The Bulk Insert Task Editor
Apr 18, 2008
Hello,
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
Thanks.
Table Column Names = Dataset Column Values?!
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!!!
Transact SQL :: Replacing Names With Stars
Oct 30, 2015I'm struggeling a bit with this one, goal is to show the first 3 characters of the name and replace the remaining characters with *** stars. Also the number of stars needs to match the lenght of the name as in the example above.I've tried:
REPLACE([ColName],
substring([Colname],
4,
LEN([colname]
)),
'*')
Transact SQL :: How To Automate Names Of Select Into Tables
Jun 3, 2015I have a number of tables, that I need to copy with data intact, about once a year.
Let's say the table name is 'dbo.MyTable'
I just do a SQL Statement like this:
SELECT * INTO dbo.MyTable'BU06032015 FROM dbo.MyTable
How can I automate this so that it does the Select Into statement and automatically adds the 'BU' + the current date?
Transact SQL :: Table Name Followed By Columns Names In A Single Row?
May 12, 2015I am able to get a list of columns in a table from the query I have written shown below:
select sc.name ColumnNames,st.name TableName from sys.columns sc inner join sys.tables st on sc.object_id=st.object_id
order by st.name
But I am looking for the resultset with the format below:
TableName Columns
employee employeeid,employeename,employeesalary
order orderid,address,price
Transact SQL :: Use Dynamic Table Names And Get Return Value From The Query
Sep 16, 2015I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
- SQL Functions cannot use Exec to execute query strings.
- SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
Transact SQL :: Special Names For Columns In Cross Tabs?
May 7, 2015While looking forward to design a multi-columnar cross-tab query I am anxious to know if there could be a way to change the default names of the pivot columns? In other words for the query like the following can there be a way to apply anAS type command to reflect some other names, instead of having the four dates in heading? Something like Month_A, Month_B?
SELECT * FROM
(SELECT
X.REP_DT,
X.CUST_ID
AMOUNT_1
FROM
X) P
PIVOT (SUM(AMOUNT_1) FOR REP_DT IN ([2014-12-31], [2015-01-31], [2015-02-28], [2015-03-31])) PVT_01