SQL Server 2012 :: Find Names Of Tables For A Particular User
Feb 18, 2015how 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
ADVERTISEMENT
SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables
Oct 7, 2015I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
SQL Server 2012 :: Query To Find User Who Last Modified User Roles / Access?
Dec 6, 2013I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
View 3 Replies View RelatedSQL 2012 :: Find All Table Names That Have Foreign Key
Oct 20, 2015Is 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
I Need A Query To Find Indexes Names For Some Kind Of Tables
Apr 9, 2008Hi,
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...
SQL Server 2012 :: Find All Tables Used In Any Stored Procedure
Feb 14, 2015I have a table with the list of all TableNames in the database. I would like to query that table and find any tables used in any stored procedure in that DB.
Select * from dbo.MyTableList
where Table_Name in
(
Select Name
From sys.procedures
Where OBJECT_DEFINITION(object_id) LIKE '%MY_TABLE_NAME%'
Order by name
)
SQL Server 2012 :: Find Common Link Between Two Tables In Database Without Key Constraints?
Jul 17, 2015CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )
What is the link between these two tables?
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
Database Tables And User Names
Jan 26, 2007We have recently copied a database from one machine to another. On the old machine, when we access the tables we do not need to use the username.tablename convention to query them. On the new box we do.
For example, to query a table called Page we would nee to do this on the new box.
SELECT *
FROM webdev.page
unfortunately all the code is written, without the username prefix. Is there a way to not use the username prefix?
Thank you for your help...sorry for the newbie type question.
SQL Server 2012 :: Find Queries That Lock Tables Or Not Using Primary Key While Running Update
Jul 20, 2015I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.
If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.
If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.
SQL 2012 :: Query To Understand Names Of All Available Tables / Number Of Records
Aug 31, 2014SQL query to understand the names of all the available tables , number of records in these tables and size of these tables?
View 4 Replies View RelatedSQL 2012 :: How To Grant A New User Same Direct Privilege To Tables As Existing User
Feb 18, 2014I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.
View 2 Replies View RelatedSQL 2012 :: Script To Find One Day Old User?
Apr 28, 2015I need to find the one day old(in last 24hrs) user details:
Below script i am using , but its not working as expected.
select name,createdate,* from syslogins where isntgroup=0 and isntname=1 and name not LIKE ('%sa-%') and name not LIKE ('%NT %') and createdate > DATEADD(hh,-24,GETDATE())
Find The List Of Tables For A User?
Feb 18, 2015how 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 RelatedHow To Find Out The List Of All Tables And Views Owned By A Particular User In Any Database?
Oct 15, 2001How to find out the list of all tables and views owned by a particular user in any database by querying the system tables?Thanks.
View 2 Replies View RelatedSQL 2012 :: Possible To Find All Tables That Has A Join With Given Table In Database
Jan 7, 2015I need to find all tables which has a join (either inside an sp, view, etc) with my given table in a db.
sys.dm_sql_referencing_entities doesn't work here.
Note: i dont want to identify by FK References, for 2 reasons:
1) tables might not met with a join (just FK was defined)
2) sometimes, a join happened between tables, without an FK defined
SQL Server 2014 :: Find Names Of Customers Who Have Purchased Academic Books?
Nov 10, 20131. 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)
Problem: Find All Table Names In Db And Then Find
Jun 12, 2008I 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)
SQL 2012 :: Nested Tables And User Defined Data Types?
Jun 5, 2015I have a requirement of creating nested tables in SQL server. how to create them. Just to give a background I am trying to move the RDBMS from oracle to SQL server.
Structure of tables is as follows. I have table 'Employees' with address as one of the column. I have one more table with columns Street, Town, Dist, State. When I query the table 'Employees' I should see the attribute name and values of all the columns in address table in address column.
Employees: with columns: ID, FirstName, LastName, dept, gender, dob, address
Address (Nested table): with columns : Street, Town, Dist, State
This was done in oracle using Nested tables and user defined data types. what is alternative for this in SQL server. How can I achive this requirement in SQL server.
SQL Server 2012 :: How To Update Fields Having Same Names And DOB
Apr 11, 2014Create Table #Temp (ID int not null primary Key, Name varchar(25) not null, DOB datetime not null, Sex char(1), Race char(1), Height int, Weight int)
insert #Temp
select 1, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', NULL,NULL, NULL, NULL
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223
[Code] .....
/* Expecting Output */
select 1 as ID, 'Kenneth' as Name, '1963-02-26 00:00:00.000' as DOB, 'M' as Sex,'C' as Race, 516 as Height, 160 as Weight
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223
union
[Code] .....
SQL 2012 :: Aliasing Column Names In Server
Jan 6, 2015Is there a way in SQL server to alias column names for a particular table and store the aliases somewhere such that you can access the aliases while querying? I have a table where I cannot change column names and I am trying to figure out if there is a way to alias them to make them more user friendly.
View 2 Replies View RelatedSQL Server 2012 :: How To Set Some Restriction On Table Names While Creating
Nov 26, 2013I need to ensure some naming standards to users while creating tables.
All table name should be in same pattern
(ie., ) TeamName_EmpId_tablename
I need a way to restrict user to follow the naming standard. If any user tries to create table without the above naming standard system should not allow.
SQL Server 2012 :: Dynamic Query On DB Table Names
Mar 9, 2015I have query which is used to dynamically insert value but not working. We are trying to get all table names and insert dynamically to all tables by looping through table names.
declare @a varchar(max),@i int;
declare @table table(rno int, name varchar(max))
declare @b varchar(max)
insert into @table
select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables
declare @tblname varchar(max)
[Code] .....
SQL Server 2012 :: Replace Names Found In XML Field Name As Hyperlinks
Nov 18, 2014I am trying to replace names found in 'xml' field name as hyperlinks, by matching with the names in database_tags.I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.
ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
[code]...
for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.But the updated function is only recognizing two of names BFTH, BFTH, as hyperlinks, from the database_tags table.Is there a way to get the function to recognize more than one names as hyperlinks.
SQL Server 2012 :: Update Temp Table Where The Column Names Are Unknown
Dec 26, 2013In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.
So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:
UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))
However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.
how can I get the column names of a Temp table dynamically while doing an Update?
SQL Server 2012 :: Add Column Names As Total And Subtotal For NULL Values?
Jan 21, 2014How do I add column names as Total and SubTotal for NULL values.
SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)
[code]....
SQL Server 2012 :: Case Statement - Populate Field Names In Table
Apr 9, 2014Is this a correct syntax to populate a field name PHONES in my CUSTOMERS TABLE
case when(d.phone = (SELECT phone from CALLS where exists(select home_phone, mobile, toll_free from CALLS2) Then 1 END 'PHONES'
Any Way To Find User Who Did Update In SQL SERVER?
Nov 21, 2007Hi there,Is there any way to find the person or user who did update transactionmanuallyin query analyser and not through application in certain time?As i have check thru log file but cannot find anything.Any command or software to check it?Thanks for the help!
View 2 Replies View RelatedSQL Server 2012 :: View To Concatenate File / Path Names Back To Root Directory?
Sep 25, 2014We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root
Below is a sample:
Id Name IdParent
1 C: NULL
2 D: NULL
3 ProgramFiles 1
4 DataFiles 2
5 Excel.EXE 3
6 MyDataList.CSV 4
iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.
I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.
Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)
The end result of my view on the above data should return:
Id FilePath
5 C:ProgramFilesExcel.EXE
6 D:DataFilesMyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)
SQL Server 2008 :: How To Delete Tables In Database Whose Table Names Match A Criteria
Jul 22, 2015The database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement, Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP' This returns the error message"Ad hoc updates to system catalogs are not allowed".
What is the correct way to delete a group of tables whose name match a pattern from within SSMS?
To Find Parameter Names
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.
To Find Out The Table Names -- Urgent
Dec 13, 2001Hi 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 RelatedDynamic Tables Names And Temporary Tables Options
Oct 5, 2007Firstly I consider myself quite an experienced SQL Server user, andamnow using SQL Server 2005 Express for the main backend of mysoftware.My problem is thus: The boss needs to run reports; I have designedthese reports as SQL procedures, to be executed through an ASPapplication. Basic, and even medium sized (10,000+ records) reportingrun at an acceptable speed, but for anything larger, IIS timeouts andquery timeouts often cause problems.I subsequently came up with the idea that I could reduce processingtimes by up to two-thirds by writing information from eachcalculationstage to a number of tables as the reporting procedure runs..ie. stage 1, write to table xxx1,stage 2 reads table xxx1 and writes to table xxx2,stage 3 reads table xxx2 and writes to table xxx3,etc, etc, etcprocedure read final table, and outputs information.This works wonderfully, EXCEPT that two people can't run the samereport at the same time, because as one procedure creates and writesto table xxx2, the other procedure tries to drop the table, or read atable that has already been dropped....Does anyone have any suggestions about how to get around thisproblem?I have thought about generating the table names dynamically using'sp_execute', but the statement I need to run is far too long(apparently there is a maximum length you can pass to it), and evenbreaking it down into sub-procedures is soooooooooooooooo timeconsuming and inefficient having to format statements as strings(replacing quotes and so on)How can I use multiple tables, or indeed process HUGE procedures,withdynamic table names, or temporary tables?All answers/suggestions/questions gratefully received.Thanks
View 2 Replies View RelatedMigration Problems From SQL 2000 To SQL 2005: User Cannot Find The User 'RSExecRole'
Jan 25, 2008
Hello,
I am trying to migrate reporting services from SQL server 2000 to SQL 2005. I followed the migration steps listed in one of the TechNet documents. Installed SQL 2005 and SP2.
Backed up existing SQL 2000 databases and restored on SQL 2005.
Went to Configuration tool and then to the Database setup. Connected to SQL 2005.
Clicked on the Upgrade button. The upgrade fails and I always get the following errors:
"The database version (C.0.6.54) does not match your reporting services installation. You must upgrade your Reporting services database"
Then I get this exception:
System.Data.SqlClient.SqlException: Cannot find the user 'RSExecRole', because it does not exist or you do not have permission.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
I do not understand why it is looking for user 'RSExecRole', which is a role.
Appreciate your help