April 21, 2011 at 9:49 pm
I had two tables
Table a table b
Name VARCHAR NumericalName VARCHAR
Company ID VARCHAR Company ID VARCHAR
I will tell you an example
TABLE A values table b values
Name company id numerical name company id
JOHN ABRAHAM abc\jabraham htryig1 abc\jabraham
george boon abc \gboon hqb56 abc\stom
hansie beaurd abc\hbeaud jqu78 abc\hpeter
i have to implement logic like this if name and numerical name are having same company id( abc\jabraham ) then take that company id and replace all the names like JOHN ABRAHAM which appears in the scripts with the name abc\jabraham.
if both names are not the same then the script should take the name from table a or table b and replace it with the company id from their respective tables. the renaming of the objects should be done in the scripts which i generate below
i will generate scripts like the following items
1) creating login statements
2) creating users
3) GRANTING ROLES
4) GRANTING PERMISSION
5) CREATING SCHEMA AND AUTHORIZE USERS
6) ASSIGNING DEFAULT SCHEMA
7) Importing OBJECTS FROM A SCHEMA TO ANOTHER SCHEMA
examples:
1)
CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;
GO
2)
USE AdventureWorks2008R2;
CREATE USER Wanida FOR LOGIN WanidaBenshoof
3)
use mydb
go
exec sp_addrolemember db_datareader, MYUSER
go
4)
USE AdventureWorks2008R2;
GRANT SELECT ON sys.sql_logins TO Sylvester1;
5)
CREATE SCHEMA [dell\john] AUTHORIZATION [dell\john]
GO
6)
USE AdventureWorks2008R2;
ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;
GO
7)
SELECT 'ALTER SCHEMA DailySales TRANSFER Sales.'+name
FROM sys.objects
WHERE type IN ('U','V','P','Fn')
AND SCHEMA_NAME(SCHEMA_ID) = 'Sales'
i need my script to check all of these statements in all of the databases and i need to replace names with company id name with my above said logic
Each case will have different statements
I need a logic which will check the results in the table and and replace the name with the company ids in all of the scripts.
April 21, 2011 at 10:23 pm
TABLE A values table b values
Name company id numerical name company id
JOHN ABRAHAM abc\jabraham htryig1 abc\jabraham
george boon abc \gboon hqb56 abc\stom
hansie beaurd abc\hbeaud jqu78 abc\hpeter
This is the table format. sorry the formating of the table was not clear . so i am reformating and sending the table format.
Could any one please help me.
Thank you
April 22, 2011 at 6:57 am
sql2k8 (4/21/2011)
TABLE A values table b valuesName company id numerical name company id
JOHN ABRAHAM abc\jabraham htryig1 abc\jabraham
george boon abc \gboon hqb56 abc\stom
hansie beaurd abc\hbeaud jqu78 abc\hpeter
This is the table format. sorry the formating of the table was not clear . so i am reformating and sending the table format.
Could any one please help me.
Thank you
Can you do this in the form of a DECLARE @test-2 TABLE or CREATE TABLE statement, and then INSERT statements to populate the data? That would really help us out a lot better. Please see the first link in my signature for more details on how to do this, and why it helps us out so much.
Edit: Additionally, how do you connect the rows between the tables? Is it positional, or are there some other columns that will match up?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 23, 2011 at 1:44 pm
yes i can use table variables and i can insert into the table variables from the tsql statements
April 23, 2011 at 1:45 pm
I did not create any foreign keys between the tables
April 23, 2011 at 1:49 pm
You cannot vote on your own post
0
I am sorry my post was not clear to you. let me be more elaborative
TABLE A values
Name company id
------------------- -----------------
JOHNABRAHAM abc\jabraham
georgeboon abc \gboon
hansiebeaurd abc\hbeaud
table b values
numerical name company id
------------------ -----------------
htryig1 abc\jabraham
hqb56 abc\stom
jqu78 abc\hpeter
I am using sql server 2008 r2 enterprise version with sql 2000 databases. I can store the statements in the table d in database e
The table d will have only one column which is called script
There is a medium domain in which we have a login JOHNABRAHAM . Like this there can be other Logins. i will generate statements like this
1)
CREATE LOGIN [Medium\JOHNABRAHAM] FROM WINDOWS;
GO
2)
USE Sony;
CREATE USER [Medium\JOHNABRAHAM] FOR LOGIN [Medium\JOHNABRAHAM]
3)
use sony
go
exec sp_addrolemember db_datareader, [Medium\JOHNABRAHAM]
go
4)
USE [Medium\JOHNABRAHAM];
GRANT SELECT ON sys.sql_logins TO [Medium\JOHNABRAHAM];
5)
CREATE SCHEMA [Medium\JOHNABRAHAM] AUTHORIZATION [Medium\JOHNABRAHAM]
GO
6)
USE sony;
ALTER USER [Medium\JOHNABRAHAM] WITH DEFAULT_SCHEMA = [Medium\JOHNABRAHAM];
GO
7)
SELECT 'ALTER SCHEMA [Medium\JOHNABRAHAM] TRANSFER JOHNABRAHAM .'+name
FROM sys.objects
WHERE type IN ('U','V','P','Fn')
AND SCHEMA_NAME(SCHEMA_ID) = ' JOHNABRAHAM '
There is a JOHNABRAHAM schema in a database sony. let me expalin with an example
The sony database was backed up in medium domain and restored in abc domain. the table a and b which i was pointing to you was a cross reference table.
I need logic which will be like this. It has to go the reference table a and b
check the name and numerical name and it has to replace with the value in company id column of table a or table b
The checking should be like this if it finds values like JOHNABRAHAM and htryig1 which have same company id abc\jabraham then it should replace JOHNABRAHAM with [abc\jabraham] in the scripts which i generate.if name and numerical name in table a and table b are not having same company id then what it should do is if it finds georgeboon in table a then it should replace it with abc \gboon
Thank You very much for taking your valuable time to help me. Please help me with the logic
April 24, 2011 at 5:05 pm
I can create table variable in this format
DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @Schema_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@DBuser_sql
Insert @Schema_table
SELECT distinct DBName,UserName, LoginType, AssociatedRole FROM @DBuser_table
April 24, 2011 at 8:23 pm
sql2k8 (4/21/2011)
TABLE A values table b valuesName company id numerical name company id
JOHN ABRAHAM abc\jabraham htryig1 abc\jabraham
george boon abc \gboon hqb56 abc\stom
hansie beaurd abc\hbeaud jqu78 abc\hpeter
This is the table format. sorry the formating of the table was not clear . so i am reformating and sending the table format.
Could any one please help me.
Thank you
First, you were asked to provide the data as a couple of CREATE TABLE statements and some readily consumable insert statements. We're still waiting for that. Please see the first link in my signature line below for what I mean and why.
Second, your description is probably OK but just to make sure, if you were to apply the changes you require to the actual tables above, what would the "after" version of those tables look like?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply