How to get list of all the tables with all the columns from SQL Server Databases on SQL Server Instance

Scenario:

Was working on a project and needed to get list of all the tables from all the databases with columns. Write below script. The script uses cursor to loop through all the databases to get the list of tables with columns. You can filter the databases on which you would like to run the query. I have ignored the system databases such as master, model, tempdb and msdb. 


USE master
GO
--Declare Variables
DECLARE @DatabaseName AS VARCHAR(500)
--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results
CREATE TABLE #Results (
    ServerName VARCHAR(128)
    ,DatabaseName VARCHAR(128)
    ,SchemaName VARCHAR(128)
    ,TableName VARCHAR(128)
    ,ColumnName VARCHAR(128)
     ,Data_Type VARCHAR(128)
   ,Is_Nullable VARCHAR(25)
   ,Character_Maximum_Length VARCHAR(10)

    )
DECLARE CUR CURSOR
FOR
--Filter the Database for which you would like to get all the tables with columns
SELECT '[' + NAME + ']' AS DBName
FROM sys.databases
WHERE NAME NOT IN (
         'master'
        ,'tempdb'
        ,'model'
        ,'msdb'
    
        )
OPEN Cur
FETCH NEXT
FROM Cur
INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Build dynamic sql for each database 
    DECLARE @SQL VARCHAR(MAX) = NULL
    SET @SQL = 'Insert into #Results
      Select
      @@ServerName,
   Table_Catalog as DatabaseName,
   Table_Schema AS TableSchema,
   Table_Name AS TableName,
   Column_Name AS ColumnName
    ,Data_Type
   ,Is_Nullable
   ,Character_Maximum_Length
  From' + @DatabaseName + '.information_Schema.Columns 
  where Table_Name in (Select Table_Name from ' + @DatabaseName + '.information_schema.Tables
  where table_type=''BASE TABLE'')'
    EXEC (@SQL)
    PRINT @SQL
    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
END
CLOSE Cur
DEALLOCATE Cur

SELECT *
FROM #Results
order by DatabaseName,TableName

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]

Scenario: SQL Server 2016 Installed successfully as per Wizard but can't find SQL Server Service and can't connect to SQL Server from SSMS.


Solution:

Today I installed SQL Server 2016 on my 64 bit machine. 


The file for installation that I used was aslso 64Bit. After installation I tried to connect to SQL Server Engine by using SSMS but could not find.

Next thing I did , I checked tried to open SQL Server configuration manager but got this error
"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.  Invalid namespace [0x8004100e]"

Then I went to Control Panel to take a look if SQL Server is installed on machine.

I found out the Size and Version was blank. Looks like the SQL server was not installed correctly. But Wizard should have given an error but it completed successfully.

When was doing research, people are talking about the file can be damaged if you have uninstalled and instance of SQL Server, that I have not done it. I did the fresh installation and it was first time.

"This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder."

Anyways to fix this, you need to run below command on cmd after making sure you have below file on below path.

The Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.



Open cmd by going to Run in windows. I am using SQL Server 2016, so my number is 130 in below statement, your will be depending upon SQL version.
and paste below command.

mofcomp "%programfiles(x86)%\Microsoft SQL Server\130\Shared\sqlmgmproviderxpsp2up.mof"


Now you can open the SQL Server Configuration Manager without any problem but you will not the SQL server Service. You have to reinstall SQL Server.

I restarted the machine and then Reinstalled SQL Server. The installation completed successfully and was able to use SQL Server without any problem.

How to check if User is Enabled or Disabled in all the databases in SQL Sever

Scenario: How to check if the database user is enabled or disabled in multiple or all the databases in SQL Server

You have created a login and then user in multiple databases by using that login. You need to take a look if the user is enable or disable in those databases. 

If the user is disabled in a database you will see a red arrow pointing downwards. I have disabled Aamir user as can be seen below by using 

REVOKE CONNECT FROM Aamir


If you want to enable the user, you can use below statement

GRANT CONNECT TO UserName

But our goal is find out in all databases or in multiple database if the user is enabled or disabled. Also if the query does not return the Database name, that means the user does not exits in that database.


--Provide the user name that you would like to check if Enabled or Disabled in Databases
    DECLARE @UserName VARCHAR(128) 
    SET @UserName='Aamir' -- I have provided Aamir as my User Name
    

  IF OBJECT_ID('tempdb..##Temp_Table') IS NOT NULL
        DROP TABLE ##Temp_Table

    CREATE TABLE ##Temp_Table (
    ServerName VARCHAR(1000),
        DatabaseName VARCHAR(500)
        ,UserName VARCHAR(500)
        ,IsEnabled INT
        )
   
    DECLARE @SQLStatement VARCHAR(MAX)
    SET @SQLStatement='Insert into ##Temp_Table
Select @@ServerName AS ServerName,DB_Name() AS DatabaseName,name, hasdbaccess 
FROM sys.sysusers WHERE name='''''+@UserName+''''''

Print @SQLStatement
    DECLARE @DatabaseName AS VARCHAR(500)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run Check If user Enabled or Disabled in Database
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@SQLStatement+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR

    Select * From ##Temp_Table