Get list of all table name along with their column name
The following MS SQL query can be used to get list of tables along with their column details.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
Source : MSDN Forum
Bits & Pieces – A blog by Kannan Balasubramanian
The following MS SQL query can be used to get list of tables along with their column details.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
Source : MSDN Forum
Scenario:
Microsoft has removed the functionality of automatically adding the local windows system admin to SQL 2008 Users. The following solutions are workaround to get inside the SQL server by adding a user to SQL Users Table.
Note: Since this involves SQLCMD and starting, stopping of SQL Instance the logged in user should be an administrator of that server where the SQL Server is running and both solutions should be run under an elevated command prompt.
Solution 1 : (Little difficult Solution. Check Solution 2 for automated script by Microsoft)
Open command prompt.
sqlservr -m”SQLCMD”
CREATE LOGIN [testAdmin] WITH PASSWORD=N’test@1234′, DEFAULT_DATABASE=[master];
EXEC sys.sp_addsrvrolemember @loginame = N’testAdmin’, @rolename = N’sysadmin’;
GO
Note: If you do not have SQL authentication enabled then you can try adding your windows user and replace setp-6 with below queries. Here <<DOMAINUSERNAME>> is placeholder for your user name
create login [<<DOMAINUSERNAME>>] from windows;
EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAINUSERNAME>>’, @rolename = N’sysadmin’;
GO;
Solution 2 : (Easy Solution)
Download the following file, extract and run the cmd file in elevated command prompt.
http://downloads.binarybits.net/blog/addselftosqlsysadmin.zip
Input:
%1 specifies the instance name to be modified. Defaults to SQLEXPRESS.
%2 specifies the principal identity to be added (in the form “<domain><user>”).
If omitted, the script will request elevation and add the current user (pre-elevation) to the sysadmin role.
If provided explicitly, the script is assumed to be running elevated already.
Method:
1) restart the SQL service with the ‘-m’ option, which allows a single connection from a box admin (the box admin is temporarily added to the sysadmin role with this start option)
2) connect to the SQL instance and add the user to the sysadmin role
3) restart the SQL service for normal connections
Output:
Messages indicating success/failure.
Note that if elevation is done by this script, a new command process window is created: the output of this window is not directly accessible to the caller.
Source: http://archive.msdn.microsoft.com/addselftosqlsysadmin/