Transact SQL :: Bulk Renaming Table Names

Aug 14, 2015

How can I bulk remove spaces within table names in the whole db?

View 3 Replies


Transact SQL :: Renaming Pivot Table Column Names Dynamically

Oct 30, 2015

I 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.

View 7 Replies View Related

Transact SQL :: Bulk Column Names Rename

Aug 4, 2015

Is there a way to bulk remove spaces from column names from all tables in a db? 

View 6 Replies View Related

Renaming Logical File Names

Jul 23, 2005

Is there a way to rename the logical file names? I'm not talkingspecifically about the physical files, because those can be changedduring a restore, but the values immediately to the left of those inEnterprise Manager such as DBName_Data and DBName_log. EnterpriseManager lets me change them during a restore, but when I do it gives anerror. Any ideas?

View 1 Replies View Related

Transact SQL :: Get Table And Column Names From XML

Jul 6, 2015

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'

View 5 Replies View Related

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

View 2 Replies View Related

SQL Server 2008 :: Renaming Logical File Names

Apr 30, 2015

Is there any danger with renaming the LOGICAL file names behind the database?

There are a bunch of databases that were restored copies and all of them have the same logical database file name. I'm trying to get some growth data so I want the logical files to be different (prefer them to match the actual database name) so I can more easily identify them.

For instance:

database_id name type_desc name physical_name
1 DLMdb1 ROWS DLMDB1 D:dlmdb1.mdf
1 DLMdb1 LOG DLMDB1_log E:dlmdb1.ldf
2 DLMdb2 ROWS DLMDB1 D:dlmdb2.mdf
2 DLMdb2 LOG DLMDB1_log E:dlmdb2.ldf
3 DLMdb3 ROWS DLMDB1 D:dlmdb3.mdf
3 DLMdb3 LOG DLMDB1_log E:dlmdb3.ldf

Am I safe to rename the logical names? I can't think of anything that references those logical file names that I would be breaking [backups, applications].

View 3 Replies View Related

Renaming Tables And Stored Procedures In Bulk !!

Feb 12, 2008

Hello Friends!!

I have a great problem !!
I have a database having around 50 tables and around 70 t0 100 stored procedures.
due to certain reason name of all the table and procedure was prefixed with "cls" ,
now i want to rename them by "tbl_" using some query !! in bulk ,
and tables have relation ships.

so how to rename them !!

is there any way that I can rename them by modifying data in any system table !?

View 6 Replies View Related

Transact SQL :: Table Name Followed By Columns Names In A Single Row?

May 12, 2015

I am able to get a list of columns in a table from the query I have written shown below:

select ColumnNames, TableName from sys.columns sc inner join sys.tables st on sc.object_id=st.object_id
order by

But I am looking for the resultset with the format below:

TableName   Columns
employee      employeeid,employeename,employeesalary
order             orderid,address,price 

View 2 Replies View Related

Transact SQL :: Use Dynamic Table Names And Get Return Value From The Query

Sep 16, 2015

I 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.

View 14 Replies View Related

Transact SQL :: Find Table And Column Names From String Data

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, 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

Transact SQL :: Import Bulk CSV Files In Database Table

Nov 12, 2015

I have more than 500 CSV files with a similar structure [Same column name and same data format]. I would like to load these files in a database table on the SQL Server 2014 database.

View 21 Replies View Related

Transact SQL :: Importing Bulk Excel Files Into A Table In 2008?

Nov 2, 2015

I have around 100 XL Files in a folder ,i want to import all the files dynamically and load all the data in a single table in sql server 2008. Without using SSIS i want to query using openrowset.

View 11 Replies View Related

Transact SQL :: How To Do Bulk Insert Into Temp Table From Text File

Sep 15, 2015

How do I do a bulk insert into a temp table from a text file. Text file looks like that:
ver_id TYPE
E57AB326-803C-436E-B491-398A255C919A 58
34D2A601-6BBA-46B1-84E1-3A805FDA3812 58
986E140C-62F1-48F1-B428-3571EBF00DA0 58

My statement looks like this:

CREATE TABLE [dbo].[tblTemp]([ver_id]  [nvarchar](255), [TYPE] [smallint]) 
BULK INSERT [dbo].[tblTemp]
FROM 'C:v2.txt'
I keep receiving errors.

The error I receive is: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (TYPE).

View 2 Replies View Related

Transact SQL :: Performing Bulk Delete With Joining A Staging Table

Jun 23, 2015

I have a large table with 100 Million records that has around 1 million duplicate records that need to be deleted.

I am running a script that creates a staging  table called,DuplicateTable that collects all the duplicates and then I want to write a an effecient delete statement.

Is it possible to write something like:

delete from OrigTable O join DuplicateTable D
on O.Key = D.key

Or do I have to run a loop on the DuplicateTable and run a delete statement record by record ?

View 4 Replies View Related

Transact SQL :: How To Bulk Insert Rows From Text File Into A Wide Table Which Has 1400 Columns

Feb 3, 2010

we can easily load a file into db tables. However, my main concern here is the number of columns in the file. A text file TEXT_1400.txt has 1400 columns. I am unable to load data to my db table using BCP or BULK INSERT commands, as maximum of 1024 columns are allowed per table in SQL Server 2008. 

We can still go ahead and create ‘Wide Table’ (a special table that holds up to 30,000 columns.  The maximum size of a wide table row is 8,019 bytes.). But when operating on wide table, BCP/BULK INSERT commands still fail. After few hours of scratching my head over BCP and BULK INSERT, I observed that while inserting BCP/BULK INSERT commands are unable to identify SPARSE columns and skip these columns, which disturbs column mapping and results in data conversion and trancation errors.
Is there any proper way to load this kind of files into the db table? 

View 6 Replies View Related

Renaming Columns Based On Data Using Transact Sql

Jan 18, 2008

A happy belated New Year to all!

Can someone please help me with the following. How do I, in SS 2000, use a value to label a column?


Col A Col B Col C Col D
CA '12/1/07' '11/1/07' '10/1/07'

I want to use the month and year of the date to rename the column.

Col A 12-2007 11-2007 10-2007
CA '12/1/07' '11/1/07' '10/1/07'

Thank you in advance for any help you can offer.


View 5 Replies View Related

Dynamically Pass Table Names And File Names To IS Package

Mar 1, 2015

I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.

The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.

View 1 Replies View Related

Determine Table Names And Column Names At Runtime?

Jan 22, 2004


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.


View 5 Replies View Related

Renaming A Table

Jul 2, 2001

I had a table and it has certain dependecies(like stored proc.).
I rename the table and created anew one with same name.this new table did not inherited all the dependecies which it should.
To say,
I had a table TAB.I renamed it to TAB_old and made a new table with name TAB.When I go to SEM and click on 'Display Dependencies' table TAB is not showing anything whereas TAB_old is stil showing all the dependecies.I even ran Update stats and sp_updatestats.
any idea what is this going on.....

View 1 Replies View Related

Error After Renaming Table

Jan 17, 2008

I renamed a table, and now whenever I try to insert data into the table I get this error:

Invalid object name 'dbo.OldTableName'.

For the life of me I can't find anything that uses the old name. There is a trigger and a couple indexes, but as far as I can see they all use the new name?

View 1 Replies View Related

Table Names And Field Names

Jan 21, 2004

I'm trying to do an update query that looks like this:







It's erroring out on the Employee prefix B.EMPLOYEE saying:

..."does not match with a table name or alias name used in the query"

Is it wrong or will it cause problems to have a field name the same as the table name?

View 5 Replies View Related

Transact SQL :: Replacing Names With Stars

Oct 30, 2015

I'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:


View 5 Replies View Related

SQL 2012 :: Permissions Lost After Renaming Table?

May 28, 2015

We have a process that uses the following method to move data quickly:

TableA = 600million records
TableB = 0 records

To "move" data from TableA to TableB

Rename TableA --> TableA_HOLD
Rename TableB --> TableA
Rename TableA_HOLD --> TableB

The problem with this is that after every rename, permission to the TableB is lost so we have to execute a statement to grant permission back to TableB after the process is complete.

My question is why is this necessary? Is the permission tied to the table in such a way that it can't use the name only?

View 9 Replies View Related

Renaming An Access Table In An SSIS Package

Sep 7, 2007

My current project requires me to both rename the MDB file for an Access database and rename the table it contains. The Access files comes in with random names, each containing one table with a specific name. Based on the table name it contains, I rename both the file and the interior table to a standard name which a later package in the process references.

A foreach container loops through all the mdb files in the applicable directory, containing a script task and a file system task. The script task uses GetOleDbSchemaTable to extract the table name, then loops through an array of table names from the client's configuration, comparing it to a similar array of constant names and getting the matching one. The file system task then uses that found name (or the original table name if a conversion is not found) to rename the file to match that standard name. So far, so good.

Now I have to rename the table within the file as well. All of the examples of code I'm finding on the 'net refernce ADOX, but I haven't been able to figure out how to use that in a script task, assuming that's what I want to do in the first place.

Anyone have any experience with doing things like this?

View 3 Replies View Related

Transact SQL :: How To Automate Names Of Select Into Tables

Jun 3, 2015

I 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?

View 5 Replies View Related

Transact SQL :: Vertical Column Names With Data?

Jul 14, 2015

I have a table as below and need getting the desired result as below

Col1 Col2 Col3

---desired result

t1 t2
Col1 A
Col2 B
Col3 C


View 3 Replies View Related

Transact SQL :: Special Names For Columns In Cross Tabs?

May 7, 2015

While 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?

X) P
PIVOT (SUM(AMOUNT_1) FOR REP_DT IN ([2014-12-31], [2015-01-31], [2015-02-28], [2015-03-31])) PVT_01

View 3 Replies View Related

Transact SQL :: How To Get All Databases Names With Its Owners In Server Instance

Jul 23, 2013

I want to get all databases with it's owner Name ,I think I need to join following script result with sys.databases or sys.sysdatabases.I want a result set  like this:

DatabaseName    db_Owner

master                  sa
pubs                     valia
pubs                     eli
pubs                    maryam
AdentureWork      eli

this script get all owners of the database that is in use:

Use DatabaseName

SELECT MemberName, RoleName,roles.type_desc,members.type_desc

View 10 Replies View Related

Transact SQL :: Bulk Insert 0 Rows Affected

May 15, 2015

I have a file which has some wind data that i am trying to import into a sql data base through bulk insert. if the script works as it supposed i should see 144 rows impacted but i see 0 rows affected. 

BULK INSERT TOWER.RAWINTERFACE_1058 FROM 'C:Temp900020150427583.txt' 

The code works if the file is large but if its small 0 rows are affected. and also if i remove the header rows then the file works again. want to understand what is going on here. i am including the screen shot of the file in notepad++. I have tried changing the row terminator to ' ' , ' ' and also tried to change the codepage but nothing seems to work. No error file is being generated either, if i give a error file option. 

View 7 Replies View Related

Transact SQL :: Format File For Bulk Insert

May 16, 2012

I need fmt(format ) file for below values


View 15 Replies View Related

Transact SQL :: Additional Column With Bulk Insert?

Aug 11, 2015

Need 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 Related

Transact SQL :: Create Server Login In Bulk

Sep 29, 2015

I need to create a T-sql script to create logins in bulk so users can access views on the database. Usernames as used in the local domain are all stored in a table.

Users already exist in the domain (let’s say: MYDOMAIN)ADSI.dbo.Ad.username  is the column wich contains  the usernames as used in the Active Directory domain  (+/- 350)Script needs to check if login already exists to make sure login is not added twice.If possible give all these users db_datareader role, in let’s say: MYDATABASE

View 7 Replies View Related

Copyrights 2005-15, All rights reserved