Query all databases
Select [name] from [sysdatabases] order by [name]
Query all tables in a database
Select [id], [name] from [sysobjects] where [type] = 'U'
We all know that [type] = 'U' is a user table, and [type] = 's' is a system table.
Based on the query result of the previous statement
Query all the fields in a table (I tried to query the fields using [name] instead of [ID]. An error is returned)
Select [name] from [syscolumns] where [id] = 1931153925
The preceding statements are used to query all tables:
Change the architecture of all tables as follows:
Architecture A and user A exist in the Database testDB. Now, the user of the database testDB is changed from A to B, and user A is deleted. The architecture is also changed from A to B, and schema A is deleted, the procedure is as follows:
1. create user B and then create architecture B;
2. Grant schema A permissions to user B, cancel user A's permissions on schema A, and delete user;
3. Change all database objects belonging to architecture A to architecture B. The Code is as follows:
Alter schema [new SCHEMA name] Old transfer schema name. [Object tables, views, or stored procedures in databases]
Alter schema [B] TRANSFER A. [object 1]
Alter schema [B] TRANSFER A. [object 2]
Alter schema [B] TRANSFER A. [object n]
4. Delete architecture.
For example:
Alter schema dbo TRANSFER danxiao1.ds _ city1